Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    108

    Unanswered: Design Or Query Issue?

    So im building a database in SQL Server Express 08

    i created 4 tables with the following:


    Code:
    Tbl_Patients
    ID
    PatientName
    ...
    
    
    Tbl_Cases
    ID
    CaseName
    PatientID
    ....
    
    
    Tbl_FlagList
    ID
    FlagName
    
    
    Tbl_L_CaFl
    ID
    CaseID
    FlagID


    so that each patient can have multiple cases, and then each case can have multiple flags, where we could choose flags from a set list.


    the flags were enabled so that we could simply query agianst certain indicators per case, like this:

    Code:
    Select CaseName 
    From Tbl_Cases 
    Where ID In 
    (
         Select CaseID 
         From Tbl_L_CaFl
         Where FlagID = 3
    )


    this worked good, untill a user brought up the request that they wanted to be able to add information, on a per case basis, to the flags.

    so basically if a case had a flag, the user wanted to be able to type in a box "WHY" that case had that particular flag. and since a case can have multiple flags, this should be linked to each flag.



    so my question is:
    1. should i modify the Tbl_L_CaFl table and add a FlagDetails field and then re-write my SQL Query to take from that?
    also...how would i go about writing that query?

    OR

    2. Should i re-design the relationship with a master-details template to the flags (ie: do away with the many-to-many)?
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
    Posted as-is.
    Any spelling and/or grammar mistakes are a direct
    result of a communication glitch between my brain
    and my fingers which may or may not be
    directly related to a lack of caffeine intake.
    =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

  2. #2
    Join Date
    May 2004
    Posts
    7
    Hi

    It all depends on the definition of Case and Flag.
    If they are look-up tables, then you may have to change the design a little bit. But if the Case table is purely associated to the Patient table, then you do not need to re-design tables/relationship however as you thought add a FlagDetails field.

    So,
    Case 1: (the Case table is purely associated to the Patient)
    PHP Code:
    IF  EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].Tbl_Patients') AND type in (N'U'))
    DROP TABLE [dbo].Tbl_Patients
    GO
    Create Table Tbl_Patients
    (
        
    id bigint identity(1,1),
        
    PatientName varchar(30NULL
    )

    IF  
    EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].Tbl_Cases') AND type in (N'U'))
    DROP TABLE [dbo].Tbl_Cases
    GO
    Create Table Tbl_Cases
    (
        
    id bigint identity(1,1),
        
    CaseName varchar(30NULL,
        
    PatientID bigint
    )

    IF  
    EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].Tbl_FlagList') AND type in (N'U'))
    DROP TABLE [dbo].Tbl_FlagList
    GO
    Create Table Tbl_FlagList
    (
        
    id bigint identity(1,1),
        
    FlagName varchar(30NULL
    )


    IF  
    EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].Tbl_L_CaFl') AND type in (N'U'))
    DROP TABLE [dbo].Tbl_L_CaFl
    GO
    Create Table Tbl_L_CaFl
    (
        
    id bigint identity(1,1),
        
    CaseID bigint,
        
    FlagID bigint,
        
    CaseFlagDesc varchar(200NULL
    )

    INSERT INTO Tbl_Patients
    SELECT 
    'Patient 1'
    UNION
    SELECT 
    'Patient 2'
    UNION
    SELECT 
    'Patient 3'
    UNION
    SELECT 
    'Patient 4'
    UNION
    SELECT 
    'Patient 5'

    INSERT INTO Tbl_Cases
    SELECT 
    'Patient 1 Case 1',1
    UNION
    SELECT 
    'Patient 1 Case 2',1
    UNION
    SELECT 
    'Patient 1 Case 3',1
    UNION
    SELECT 
    'Patient 1 Case 2',1
    UNION
    SELECT 
    'Patient 2 Case 1',2
    UNION
    SELECT 
    'Patient 2 Case 2',2
    UNION
    SELECT 
    'Patient 2 Case 3',2
    UNION
    SELECT 
    'Patient 2 Case 2',2

    INSERT INTO Tbl_FlagList
    SELECT 
    'Flag 1'
    UNION
    SELECT 
    'Flag 2'
    UNION
    SELECT 
    'Flag 3'
    UNION
    SELECT 
    'Flag 4'
    UNION
    SELECT 
    'Flag 5'

    INSERT INTO Tbl_L_CaFl
    SELECT 1
    1NULL
    UNION
    SELECT 1
    2NULL
    UNION
    SELECT 1
    3NULL
    UNION
    SELECT 5
    1NULL
    UNION
    SELECT 5
    2NULL
    UNION
    SELECT 6
    1NULL

    SELECT 
    *
    FROM Tbl_Patients a
        JOIN Tbl_Cases b
        ON a
    .ID=b.PatientID
        JOIN Tbl_L_CaFl c
        ON b
    .ID=c.CaseID
        JOIN Tbl_FlagList d
        ON c
    .FlagID=d.ID
    ORDER BY PatientName

    UPDATE Tbl_L_CaFl
    SET CaseFlagDesc
    ='Patient 1 Case 1'
    WHERE CaseID=and FlagID=
    Case 2: (Case and Flag are look-up tables)
    As they are look-up tables, PatientID is moved to the bridge table(Tbl_L_CaFl).
    PHP Code:
    IF  EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].Tbl_Patients') AND type in (N'U'))
    DROP TABLE [dbo].Tbl_Patients
    GO
    Create Table Tbl_Patients
    (
        
    id bigint identity(1,1),
        
    PatientName varchar(30NULL
    )

    IF  
    EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].Tbl_Cases') AND type in (N'U'))
    DROP TABLE [dbo].Tbl_Cases
    GO
    Create Table Tbl_Cases
    (
        
    id bigint identity(1,1),
        
    CaseName varchar(30NULL
    )

    IF  
    EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].Tbl_FlagList') AND type in (N'U'))
    DROP TABLE [dbo].Tbl_FlagList
    GO
    Create Table Tbl_FlagList
    (
        
    id bigint identity(1,1),
        
    FlagName varchar(30NULL
    )


    IF  
    EXISTS (SELECT FROM sys.objects WHERE object_id OBJECT_ID(N'[dbo].Tbl_L_CaFl') AND type in (N'U'))
    DROP TABLE [dbo].Tbl_L_CaFl
    GO
    Create Table Tbl_L_CaFl
    (
        
    id bigint identity(1,1),
        
    CaseID bigint,
        
    FlagID bigint,
        
    PatientID bigint,
        
    CaseFlagDesc varchar(200NULL
    )

    INSERT INTO Tbl_Patients
    SELECT 
    'Patient 1'
    UNION
    SELECT 
    'Patient 2'
    UNION
    SELECT 
    'Patient 3'
    UNION
    SELECT 
    'Patient 4'
    UNION
    SELECT 
    'Patient 5'

    INSERT INTO Tbl_Cases
    SELECT 
    'Case 1'
    UNION
    SELECT 
    'Case 2'
    UNION
    SELECT 
    'Case 3'
    UNION
    SELECT 
    'Case 4'
    UNION
    SELECT 
    'Case 5'
    UNION
    SELECT 
    'Case 6'
    UNION
    SELECT 
    'Case 7'
    UNION
    SELECT 
    'Case 8'

    INSERT INTO Tbl_FlagList
    SELECT 
    'Flag 1'
    UNION
    SELECT 
    'Flag 2'
    UNION
    SELECT 
    'Flag 3'
    UNION
    SELECT 
    'Flag 4'
    UNION
    SELECT 
    'Flag 5'

    INSERT INTO Tbl_L_CaFl
    SELECT 1
    11,NULL
    UNION
    SELECT 2
    21,NULL
    UNION
    SELECT 3
    31,NULL
    UNION
    SELECT 5
    12,NULL
    UNION
    SELECT 5
    22,NULL
    UNION
    SELECT 6
    12,NULL

    SELECT 
    *
    FROM Tbl_Patients a
        JOIN Tbl_L_CaFl b
        ON a
    .ID=b.PatientID
        JOIN Tbl_Cases c
        ON b
    .CaseID=c.ID
        JOIN Tbl_FlagList d
        ON b
    .FlagID=d.ID
    ORDER BY PatientName

    UPDATE Tbl_L_CaFl
    SET CaseFlagDesc
    ='Patient 1 Case 1'
    WHERE CaseID=and FlagID=and PatientID=
    Try it if this is what you expected.

Posting Permissions

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