Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: Database model issue

    I need input in how to define a proper model for a certain issue.
    I've got a table, testcase. Each row in table testcase relates to 1 or more statistics tables. (the statistics differ a lot , thats way I can't place them in the very samt table) Now, there are like 50 different statistics tables. And each row may only store statistics in some of them. Whats a proper model for this?

    Ex: create foregin keys in testcase table (for all statistics thats like 50 foreign keys) that has a primary key in each statistics table. I then must have a dummy value in each statistics table to ensure that we do not violate the referencial integrety.

    Ex: create a relation between prim.key of table testcase that has a foreign key in each statistics table.

    ex: many-to-many, but this violates the referential integrety...since we relates to multiple tables on one side.

    suggestions?
    /

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You need a supertype table to pull all the different statistic types together, like this:
    Code:
    create table statistic
    ( stat_id integer primary key
    , stat_type integer not null -- indicates which subtable is relevant
    );
    Each of the 50 statistics sub-tables then has a 1:1 relationship to the supertype:
    Code:
    create table statistic1
    ( stat_id references statistic primary key
    , ... -- other cols
    );
    
    create table statistic2
    ( stat_id references statistic primary key
    , ... -- other cols
    );
    
    ...etc.
    Then rather than having 50 foreign keys in your test_case table, you create an intersection table to allow a m:m relationship between test_case and statistic:
    Code:
    create table test_case
    ( test_case_id integer primary key
    , ...
    );
    
    create table test_case_statistic
    ( test_case_id references test_case
    , stat_id references statistic
    , primary key (test_case_id, stat_id)
    );
    Of course, each statistic now needs to have an entry in 2 tables: the supertype and one of the sub-types.

  3. #3
    Join Date
    Sep 2004
    Posts
    5
    But, then I have to disable relational integrety between supertype and subtype tables? When adding data to supertype table (statistics), a related record is required in EACH of the sub-tables (ie statistics1, 2...50).

    I've created a reference between sub-tables and supertype table; stat_id (prim key in sub-type) has a ref. to prim key stat_id in supertype table.

    Am I missing something?

  4. #4
    Join Date
    Sep 2004
    Posts
    5

    Database model

    But, then I have to disable relational integrety between supertype and subtype tables? When adding data to supertype table (statistics), a related record is required in EACH of the sub-tables (ie statistics1, 2...50). And all of the sub-tables won't contain statistics for a perticular testcase.

    I've created a reference between sub-tables and supertype table; stat_id (prim key in sub-type) has a ref. to prim key stat_id in supertype table.

    Am I missing something?

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, each supertype row corresponds to a row in exactly one of the subtype tables. So to create a new "type 42" statistic with ID 1234 you would do this:
    Code:
    insert into statistic (stat_id, stat_type) values (1234, 42);
    insert into statistic42 (stat_id, ...) values (1234, ...);
    Then to associate that "type 42" statistic with test case 37:
    Code:
    insert into test_case_statistic (test_case_id, stat_id) values (37, 1234);
    Here is a very rough attempt to show an ERD for this model:
    Code:
    +---------+
    |TEST_CASE|
    +---------+
         |
    
         |
         |
         ^
    +-------------------+        +---------+              +-----------+
    |TEST_CASE_STATISTIC|>--- - -|STATISTIC|- - - --------|STATISTIC1 |
    +-------------------+        |         |              +-----------+
                                 |         |              +-----------+
                                 |         |- - - --------|STATISTIC2 |
                                 |         |              +-----------+
                                 |         |              +-----------+
                                 |         |- - - --------|STATISTIC3 |
                                 |         |              +-----------+
                                 ...........
                                 |         |              +-----------+
                                 |         |- - - --------|STATISTIC50|
                                 +---------+              +-----------+

  6. #6
    Join Date
    Sep 2004
    Posts
    5
    At first, i've created the references wrong (between sub and super type). Now it works fine. Thank you very much!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •