Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2004

    Unanswered: Datbase 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.


  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    Instead of 50 I created 5, but I don't see a problem having 50 foreign keys for your situation:
    use pubs
    set nocount on
    begin tran
    create  table dbo.t1 (
       [ID] int identity(1,1) constraint PK_ID1 primary key not null,
       f1   int not null)
    create  table dbo.t2 (
       [ID] int identity(1,1) constraint PK_ID2 primary key not null,
       f1   int not null)
    create  table dbo.t3 (
       [ID] int identity(1,1) constraint PK_ID3 primary key not null,
       f1   int not null)
    create  table dbo.t4 (
       [ID] int identity(1,1) constraint PK_ID4 primary key not null,
       f1   int not null)
    create  table dbo.t5 (
       [ID] int identity(1,1) constraint PK_ID5 primary key not null,
       f1   int not null)
    create table dbo.tbl (
       RecordID int identity(1,1) not null,
       ID1 int constraint FK_ID1 foreign key references dbo.t1 (ID) null,
       ID2 int constraint FK_ID2 foreign key references dbo.t2 (ID) null,
       ID3 int constraint FK_ID3 foreign key references dbo.t3 (ID) null,
       ID4 int constraint FK_ID4 foreign key references dbo.t4 (ID) null,
       ID5 int constraint FK_ID5 foreign key references dbo.t5 (ID) null,
       f1  int not null)
    insert dbo.t1 (f1) select 10 union select 20 union select 30
    insert dbo.t2 (f1) select 100 union select 200 union select 300
    insert dbo.t3 (f1) select 1000 union select 2000 union select 3000
    insert dbo.t4 (f1) select 10000 union select 20000 union select 30000
    insert dbo.t5 (f1) select 100000 union select 200000 union select 300000
    insert dbo.t1 (f1) select 110 union select 120 union select 130
    insert dbo.t2 (f1) select 1100 union select 2100
    insert dbo.t3 (f1) select 11000
    insert dbo.t5 (f1) select 1100000 union select 2100000
    insert dbo.tbl (ID1,ID2, ID3, ID4,ID5, f1)
       select NULL, NULL, NULL, NULL, 5, 1000 union
       select NULL, NULL, NULL, 3, 4, 1000 union
       select NULL, NULL, 4, NULL, NULL, 1000 union
       select NULL, 5, NULL, NULL, 1, 1000 union
       select 1, NULL, 4, NULL, 1, 1000
    set nocount off
    select * from dbo.tbl
    rollback tran
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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