Forum Stats

  • 3,758,414 Users
  • 2,251,384 Discussions
  • 7,870,187 Comments

Discussions

Organize Data Base & use a the value of a Varchar as column name.

User_K3U3T
User_K3U3T Member Posts: 4 Green Ribbon

I am making a database for a fitness band for a school project.

And there is a table for the exercises, but the exercises don't share the same fields(for example Running has distance and duration but push-ups only have the number of push-ups). We came up with some ideas, cause we didn't want a table with a bunch of null fields.

Idea1: We would make a type of exercise table linked to the exercise one, which would store the column names of the generic fields, But he failed to use varchar as a column name in select as.

Idea2: We would use a case to make the right " select as " with the correct column names

What is the right way to solve this type of problem, and if it is the first one, how can I implement it?

Answers

  • Hub Tijhuis
    Hub Tijhuis Member Posts: 117 Silver Badge

    I suggest either accepting the null fields (and a type_of_exercise field ) or define different tables for the different exercises.

    With method 1 you can define views for the different exercises (and you only need select * of view) , with method 2 you can use select * of table and als create a view containing all exercises.

    Using dynamic selects takes probably far more effort.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,047 Red Diamond

    Hi, @User_K3U3T

    I agree with Hub, except for the part about

    Using dynamic selects takes probably far more effort.

    Dynamic SQL definitely takes far more effort, and is less efficient. What is so bad about NULLs? (You may have a good reason for wanting to avoid them, but how to solve the problem depends on what the problem is.)

    What is the purpose of this table? What kinds of questions do you want it to answer? As Hub suggested, you may want to have more than one table. For example, you may one table that is common to all kinds of exercise (who it did, when, what kind of exercise) and others for details about the specific type of exercise.

  • User_H3J7U
    User_H3J7U Member Posts: 473 Bronze Trophy

    Dynamic SQL definitely takes far more effort, and is less efficient

    "Static" is the concept of compilation pl/sql, not execution. There is no reason to write dynamic sql in a less efficient way than static sql.

  • User_K3U3T
    User_K3U3T Member Posts: 4 Green Ribbon
    edited Apr 27, 2021 8:46PM

    we thought of dynamic SQL because we came from c where memory worked differently, but we tried to avoid a table for each exercise because we would up to 100 to 200 tables, maybe nulls is the right option here.

    What we initially had was a table with the common fields + 6 generic fields, linked to a type_exercise table that specifies the name of that fields.

    And the teacher asked us if possible to put the queries inside functions, but I can´t manage to return a table and show it on the queries tab. for what I have searched this has to do with the reference cursor, could you provide any website with good intel on this subject.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,047 Red Diamond

    Hi, @User_K3U3T

    And the teacher asked us if possible to put the queries inside functions, but I can´t manage to return a table and show it on the queries tab.

    That sounds like you're being taught to use some particular application that expects all queries to be dynamic. Designing tables that will only be used with that application may (but won't necessarily) be different from designing tables that can be used in any application.

  • User_K3U3T
    User_K3U3T Member Posts: 4 Green Ribbon

    the project is for a fitness band, and we have to make queries for daily objects, exercises... with different objectives. For example, list all the daily values between two dates for this user. Since we only have been through the basic syntax, I can´t imagine how the database will work with the java app(10% of the work- just to show that it can be used by other languages) and I don´t understand how a query like this one (select MAX( RC ) as rc_max from cardiac_rhythm  where costumer_uuid='TESTE';) will work as generic queries for any user.

    Maybe I am overcomplicating this, cause this is my first project on a database language.

  • User_H3J7U
    User_H3J7U Member Posts: 473 Bronze Trophy
    -- common table
    create table universum(
     app varchar2(20 char) not null,
     tbl varchar2(128 byte) not null,
     n1 number,
     n2 number,
     n3 number,
     s1 varchar2(4000 byte),
     s2 varchar2(4000 byte),
     s3 varchar2(4000 byte),
     d1 date,
     d2 date,
     d3 date
    );
    
    -- exercise app1 "table" tbl1
    create or replace view app1_tbl1
    (id, name, salary, dt)
    as
    select n1, s1, n2, d1
    from universum
    where app='app1' and tbl = 'tbl1'
    with check option;
    
    create or replace trigger app1_tbl1 instead of insert on app1_tbl1 
    declare
    begin
     insert into universum(app, tbl, n1, s1, n2, d1) values('app1', 'tbl1', :new.id, :new.name, :new.salary, :new.dt);
    end;
    /
    -- exercise app1 dml...
    insert into app1_tbl1(id, name, salary, dt) values (101, 'one', 1000, sysdate); 
    commit;
    
    col name format a20
    select * from app1_tbl1;
    
           ID NAME                    SALARY DT                
    ---------- -------------------- ---------- -------------------
          101 one                       1000 2021-04-28 10:35:30
    
    update app1_tbl1 set salary = salary * 2 where id = 101;
    commit;
    
    select * from app1_tbl1;
    
           ID NAME                    SALARY DT                
    ---------- -------------------- ---------- -------------------
          101 one                       2000 2021-04-28 10:35:30
    
    delete app1_tbl1 where id = 101;
    commit;
    


  • BluShadow
    BluShadow Member, Moderator Posts: 41,383 Red Diamond

    If I were designing such a database, I would look at proper relational table structure such as the following:

    -- Just examples - real tables would use sequence generated primary keys from triggers for example.
    -- Table of Exercises
    create table exercise (
      exerciseNo   number -- primary key
     ,exerciseName varchar2(20)
     )
    /
    insert into exercise values (1, 'Running')
    /
    insert into exercise values (2, 'Push Ups')
    /
    insert into exercise values (3, 'Squats')
    /
    insert into exercise values (4, 'Swimming')
    /
    -- Table of Elements
    create table element (
      elementNo    number -- primary key
     ,elementName  varchar2(20)
     )
    /
    insert into element values (1, 'Distance')
    /
    insert into element values (2, 'Duration')
    /
    insert into element values (3, 'Quantity')
    /
    -- Table of Elements relating to each Exercise
    create table exerciseElement (
      exelNo       number -- primary key
     ,exerciseNo   number
     ,elementNo    number
     )
    /
    insert into exerciseElement values (1, 1, 1) -- Running Distance
    /
    insert into exerciseElement values (2, 1, 2)-- Running Duration
    /
    insert into exerciseElement values (3, 2, 3) -- Push Ups Quantity
    /
    insert into exerciseElement values (4, 3, 3) -- Squats Quantity
    /
    insert into exerciseElement values (5, 4, 2) -- Swimming Duration
    /
    -- Table of People
    create table person (
      personNo     number
     ,personName   varchar2(20)
     )
    /
    insert into person values (1, 'Rod')
    /
    insert into person values (2, 'Jane')
    /
    insert into person values (3, 'Freddy')
    /
    -- Table of Exercises for Person
    create table personExercise (
      personNo     number
     ,exelNo       number
     ,quantity     number
     )
    /
    insert into personExercise values (1,1,10) -- Rod, Running Distance
    /
    insert into personExercise values (1,2,65) -- Rod, Running Duration
    /
    insert into personExercise values (2,3,50) -- Jane, Push Up Quantity
    /
    insert into personExercise values (2,5,30) -- Jane, Swimming Duration
    /
    insert into personExercise values (3,4,100) -- Freddy, Squats Quantity
    /
    insert into personExercise values (3,5,40) -- Freddy, Swimming Duration
    /
    commit
    /
    select per.personName, ex.exerciseNo, ex.exerciseName, el.elementNo, el.elementName, pex.quantity
    from   person per
           join personExercise pex on (pex.personNo = per.personNo)
           join exerciseElement eel on (eel.exelNo = pex.exelNo)
           join exercise ex on (ex.exerciseNo = eel.exerciseNo)
           join element el on (el.elementNo = eel.elementNo)
    /
    PERSONNAME           EXERCISENO EXERCISENAME          ELEMENTNO ELEMENTNAME            QUANTITY
    -------------------- ---------- -------------------- ---------- -------------------- ----------
    Rod                           1 Running                       2 Duration                     65
    Rod                           1 Running                       1 Distance                     10
    Jane                          4 Swimming                      2 Duration                     30
    Jane                          2 Push Ups                      3 Quantity                     50
    Freddy                        4 Swimming                      2 Duration                     40
    Freddy                        3 Squats                        3 Quantity                    100
    
    
    6 rows selected.
    
    
    -- Aggregate the elements of the exercise, in this case aggregate as strings, but it could be structured e.g. XML, JSON etc.
    select per.personName
          ,max(ex.exerciseName)||' ('||listagg(el.elementName||':'||pex.quantity,';') within group (order by ex.exerciseNo)||')' as exercise
    from   person per
           join personExercise pex on (pex.personNo = per.personNo)
           join exerciseElement eel on (eel.exelNo = pex.exelNo)
           join exercise ex on (ex.exerciseNo = eel.exerciseNo)
           join element el on (el.elementNo = eel.elementNo)
    group by per.personNo, per.personName, ex.exerciseNo
    /
    PERSONNAME           EXERCISE
    -------------------- --------------------------------------------------
    Rod                  Running (Distance:10;Duration:65)
    Jane                 Push Ups (Quantity:50)
    Jane                 Swimming (Duration:30)
    Freddy               Squats (Quantity:100)
    Freddy               Swimming (Duration:40)
    
    
    -- we could also go on to aggregate the exercises of the person if required
    drop table exercise;
    drop table element;
    drop table exerciseElement;
    drop table person;
    drop table personExercise;
    
    
    


    This obviously doesn't give dynamic individual columns for the results, as dynamic projection is not native to the SQL language (see https://community.oracle.com/tech/developers/discussion/2311172/pl-sql-101-cursors-and-sql-projection)

    As for your teachers question about putting queries in functions this depends on what the "output" of those functions should be. You could look at obtaining values and passing back individual values, or a structured data (XML, JSON etc.), or you could pass back a ref cursor (if the application accepts and processes those) or you could use a pipelined table function. So yes, you could get a function to provide the results, but what type of function depends on the consumer of the data that calls it.

    user13328581