locked
Suggestion need on DB design RRS feed

  • Question

  • I am designing a DB for Laboratory Application.

    Here  are  some of the tables. Plz let me know the changes required in the tables.

    I have to show the list of the individual options under the Test

    For ex LipidProfile is a test. Under this there will be 4 options.

    1. Cholesterol

    2. Direct LDL-C

    3. Lp-PLA2

    4. LipidProfile.

    If the  user selects LipidProfile remaining 3 options must be included. Like this there will be no of tests.

    Category:

    CREATE
     
    TABLE [dbo].[Testcategory](
    [Category_id] [int]
    IDENTITY(1,1) NOT NULL,
    [Categoryname] [varchar]
    (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Description] [varchar]
    (75) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Datecreated] [datetime]
    NOT NULL,
     
    CONSTRAINT [Testcategory_Category_id_primarykey] PRIMARY KEY CLUSTERED
    (
    [Category_id]
    ASC
    )
     
    WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    )
     
    ON [PRIMARY]

    SampleData:

    Sample Data:

     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 1,'BIOCH','BIOCHEMISTRY',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 2,'HAEMA','HAEMATOLOGY',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 3,'STOOL','STOOL',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 4,'URINE','URINE EXAMINATION',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 5,'CLINI','CLINICAL CHEMISTRY',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 6,'LIPID','LIPID PROFILE',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 7,'LIVER','LIVER FUNCTION TEST',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 8,'SEMEN','SEMEN ANALYSIS',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 9,'HORMO','HORMONE ANALYSIS',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 10,'SEROL','SEROLOGY',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 11,'TORCH','TORCH PROFILE',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 12,'URINE001','URINE CHEMISTRY',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 13,'MICRO','MICROBIOLOGY',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 14,'RADIO','RADIO & ENZYME IMMUNO ASSAYS',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 15,'HISTO','HISTOPATHOLOGY',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 16,'GTT','GTT',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 17,'CULTU','CULTURE REPORT',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 18,'BACTE','BACTERIOLOGY',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 19,'BLOOD','BLOOD GLUCOSE TOLERANCE TEST',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 20,'BODY','BODY FLUID',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 21,'DRUG','DRUG ASSAYS',Dec  6 2010  3:53PM
     insert Testcategory ( Category_id,Categoryname,Description,Datecreated )  select 22,'LIVER002','LIVER ASPIRATE',Dec  6 2010  3:53PM

    Tests Along with Fees:

    CREATE
     
    TABLE [dbo].[TestsFee](
    [testid] [int]
    IDENTITY(1,1) NOT NULL,
    [Category_id] [int]
    NOT NULL,
    [Testname] [varchar]
    (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Fee] [decimal](18, 2)
    NOT NULL,
    [Units] [varchar]
    (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [lbound] [varchar]
    (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ubound] [varchar]
    (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [defval] [varchar]
    (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [isselected] [bit]
    NULL,
    [datecreated] [datetime]
    NULL,
     
    CONSTRAINT [testid_testsfee_pri] PRIMARY KEY CLUSTERED
    (
    [testid]
    ASC
    )
     
    WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    )
     
    ON [PRIMARY]
    GO
    SET
     
    ANSI_PADDING OFF
    GO
    USE
     
    [Laboratory]
    GO
    ALTER
     
    TABLE [dbo].[TestsFee] WITH CHECK ADD CONSTRAINT [category_id_foreignkey] FOREIGN KEY([Category_id])

    Sample Data:

     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 1,1,'Blood Sugar (F)',35,'mgs','Normal: 70','100','mgs/100ml',0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 3,1,'Blood Sugar (PP)',30,'mgs','Normal: 100','140','mgs/100ml',0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 4,1,'Blood Sugar (R)',30,'mgs','Normal: 80','120','mgs/100ml',0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 5,1,'S.G.O.T',40,'','','','',0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 6,1,'S.G.P.T',45,'',null,null,'',0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 10,1,'BILI RUBIN',85,'mg%','0.1','1.0',null,0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 11,1,'BILI RUBIN (DIRECT)',160,'mg%','UPTO','0.3',null,0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 12,1,'BILI RUBIN (INDIRECT)',160,'mg%','UPTO','0.5',null,0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 14,1,'PROTEINS',70,'gm%','6.5','8.0',null,0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 15,1,'A/G RATIO',100,'','1.2.1','1.2.5',null,0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 16,6,'LIPID PROFILE',50,null,null,null,null,0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 17,6,'Cholesterol',0,null,null,null,null,0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 19,6,'Direct LDL-C',0,null,null,null,null,0,null
     insert TestsFee ( testid,Category_id,Testname,Fee,Units,lbound,ubound,defval,isselected,datecreated )  select 20,6,'Lp-PLA2 ',0,null,null,null,null,0,null

    Patient:

    CREATE

     

    TABLE

     

    [dbo].[Patient](

    [PatientID] [int]

    IDENTITY

     

    (1,1) NOT NULL,

    [TokenNo] [tinyint]

    NOT

     

    NULL,

    [PatientName] [varchar]

    (

     

    100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Gender] [varchar]

    (

     

    6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Age] [varchar]

    (

     

    50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [FeeType] [varchar]

    (

     

    15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [EnteredBy] [varchar]

    (

     

    80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [EntryDate] [datetime]

    NOT

     

    NULL,

     

    CONSTRAINT

     

    [Patient_PatientID_Primarykey] PRIMARY KEY CLUSTERED

    (

    [PatientID]

    ASC

    )

     

    WITH

     

    (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    )

     

    ON

     

    [PRIMARY]

    GO

    SET

     

    ANSI_PADDING

     

    OFF

    PatientTests:

    GO

    /****** Object: Table [dbo].[PatientTests] Script Date: 12/30/2010 10:43:27 ******/

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    SET

     

    ANSI_PADDING OFF

    GO

    CREATE

     

    TABLE [dbo].[PatientTests](

    [pid] [int]

    IDENTITY(1,1) NOT NULL,

    [PatientID] [int]

    NOT NULL,

    [TestID] [int]

    NOT NULL,

    [Referencedoctor] [varchar]

    (70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [TestDate] [datetime]

    NOT NULL,

     

    CONSTRAINT [PatientTests_pid_primary] PRIMARY KEY CLUSTERED

    (

    [pid]

    ASC

    )

     

    WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    )

     

    ON [PRIMARY]

    GO

    SET

     

    ANSI_PADDING OFF

    GO

    USE

     

    [Laboratory]

    GO

    ALTER

     

    TABLE [dbo].[PatientTests] WITH CHECK ADD CONSTRAINT [PatientTests_PatientID_foreignkey] FOREIGN KEY([PatientID])

    REFERENCES

     

    [dbo].[Patient] ([PatientID])

    ON

     

    DELETE CASCADE

    GO

    ALTER

     

    TABLE [dbo].[PatientTests] WITH CHECK ADD CONSTRAINT [TestsFee_TestID_foreignkey] FOREIGN KEY([TestID])

    REFERENCES

     

    [dbo].[TestsFee] ([testid])

    ON

     

    DELETE CASCADE

    Payment:

    GO

    /****** Object: Table [dbo].[Payment] Script Date: 12/30/2010 10:44:07 ******/

    SET

     

    ANSI_NULLS ON

    GO

    SET

     

    QUOTED_IDENTIFIER ON

    GO

    SET

     

    ANSI_PADDING OFF

    GO

    CREATE

     

    TABLE [dbo].[Payment](

    [PaymentID] [int]

    IDENTITY(1,1) NOT NULL,

    [PatientID] [int]

    NOT NULL,

    [IndividualTestFee] [varchar]

    (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [TotalAmount] [int]

    NOT NULL,

    [Concession] [int]

    NULL,

    [PaidAmount] [int]

    NULL,

    [PaidDate] [datetime]

    NULL,

    [BalanceAmount] [int]

    NULL,

    [BalancePaidAmount] [int]

    NULL,

    [BalancePaidDate] [datetime]

    NULL,

     

    CONSTRAINT [Payment_PaymentID_Pri] PRIMARY KEY CLUSTERED

    (

    [PaymentID]

    ASC

    )

     

    WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

    )

     

    ON [PRIMARY]

    GO

    SET

     

    ANSI_PADDING OFF

    GO

    USE

     

    [Laboratory]

    GO

    ALTER

     

    TABLE [dbo].[Payment] WITH CHECK ADD CONSTRAINT [Payment_PatientID_Foreign] FOREIGN KEY([PatientID])

    REFERENCES

     

    [dbo].[Patient] ([PatientID])

    ON

     

    DELETE CASCADE

     

    Thursday, December 30, 2010 5:16 AM

Answers

  • If so, design looks OK

    1.Categories

    2.Fee

    3.Patient

    4.PatientTests


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 30, 2010 8:12 AM
  • Hi Baba urf Murari,

    If I understand your requirements correctly, your problem is very
    similar to the "Bill of Material". If you google for "bill of material
    database design", you'll find lots of ideas and suggestions.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, December 30, 2010 11:29 AM

All replies

  • I am not sure what you are asking for. Where is the table named such Test_Details that includes TestID,PaymentID, CategoryID and etc..?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 30, 2010 5:57 AM
  • Hi Dimant

      I requested that the DB which i represented above is the rt one or not.

    PatientTests table consists the testdetails

    columns are

    PatientID,TestID,Referencedoctor,TestDate.

    My request is that I may select Entire test or i may choose individual options in the test.

    For ex LipidProfile is a test. Under this there will be 4 options.

    1. Cholesterol

    2. Direct LDL-C

    3. Lp-PLA2

    4. LipidProfile.

    Here i may choose LipidProfile or i may choose the individual options 1,2 or 3.

    If the  user selects LipidProfile remaining 3 options must be included. Like this there will be no of tests available.

    If the user selects  the options 1,2 or 3 the i have to get the

    fee of the LipidProfile. How i can achieve this. Plz tell me that information is not cforrect or u r expecting more business rules.

    Thursday, December 30, 2010 6:12 AM
  • Are you asking to build SELECT statement? Where  those option should be inclided? In final result? Can you show an expected result?
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 30, 2010 6:42 AM
  • One of my request is that the tables are designed in properway or not.I try to update  the expected result after some time.

    Thank You.

                                                                              

    Thursday, December 30, 2010 7:00 AM
  • If so, design looks OK

    1.Categories

    2.Fee

    3.Patient

    4.PatientTests


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 30, 2010 8:12 AM
  • If so, design looks OK

    1.Categories

    2.Fee

    3.Patient

    4.PatientTests


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, December 30, 2010 8:13 AM
  • Hi Baba urf Murari,

    If I understand your requirements correctly, your problem is very
    similar to the "Bill of Material". If you google for "bill of material
    database design", you'll find lots of ideas and suggestions.


    Hugo Kornelis, SQL Server MVP
    My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
    Thursday, December 30, 2010 11:29 AM