Design a Lab Report tables [closed]

I need to design tables for a laboratory test.

I will have Lab test name, and its component to be saved. The component can be changed with time. I need to design a UI for update. Also each component’s data with name, min, max value etc. needs to be saved in tables.

I already have Person table for storing PersonID there.

How can I design this more efficiently, Any help. Thanks.

I think one possible design with 4 table, If I understand your requirement.

Table LabTest

TestId (PK) - INT 
TestName  - varchar(100)
Status - bit (active/inactive)
..Other Columns

Table LabComponent

ComponentId (PK) - INT/BigInt
TestId - (FK) INT
ComponentName  - varchar(100)
Min - varchar(20)-- or any other data types
Max - varchar(20) -- or any other data types
Status - bit (0/1)
..Other Columns

Table LabTestReport

ReportId (PK) BigInt/Int
TestId (FK) BigInt/Int
PersonId (FK) -- as stated in question
Status - bit (0/1)
..Other Columns like date,etc

Table LabComponentReport

ReportId (FK) -- BigInt/Int
ComponentId(FK) -- Int/BigInt
Value - varchar(20)-- or any other  data types
Status - bit (0/1)

For adding any new test make an entry in LabTest table and add it’s corresponding component in table LabComponent. Now when a person is adding any report make an entry in LabTestReport table first and then make corresponding component entry in LabComponentReport. PK Id in the table, you can make them Auto incrementing.

I have added Some datatype you can consider them to change as per the requirement, like Int to BigInt etc.

When creating a data model a good place to start is to have an entity type for each noun in the description of your problem. The attributes of these entity types will be the values you wish to store about those nouns and verbs in the description will become relationships in your data model.

In the database the entity types become tables, the attributes are columns and relationships end up as foreign keys. Of course there are a lot of subtlties but this should get you going.

Copying your description above into these terms you will have:

Table: LabTests
    Name

Table: Components
    Name
    MinValue
    MaxValue

Since you say “component can be changed with time” you will need a table to link the two together

Table: TestComponents
    TestName
    ComponentName
    FromDate
    ToDate

I’ve included FromDate and ToDate so you can tell how a test has changed over time. If you are only interested in what a test looks like right now you can remove these columns.

You mention PersonID, so we’ll have a table to put that into.

Table: People
    PersonID

What do you do to that person? You don’t say, but I should think you take a sample from them and apply tests to that sample. OK, let’s have tables for those.

Table: Samples
    SampleID
    TakenFromPersonID
    TakenDateTime

You will need to know from whom the sample was collected. This is the TakenFromPersonID. You will need to know when it was collected. Since it is likely that several samples can be collected in one day this will have to be measured to the minute, or whatever is appropriate. I’ve added SampleID so you have a unique, anonymous values which can be used to track sample through the lab, used on bar codes and things like that. You don’t say but I think you may want to have a SampleMaterial column to distinguish between, say, blood and urine samples because some tests will be applicable to one but not the other.

Now we have the sample in the lab we need to know what tests to perform. I will assume you do not perform every test on every sample. You need a table to hold this information.

Table: OrderedTest
    SampleID
    TestName

Perhaps you will need to record who ordered the test and when. You can add to this table as required.

Knowing the TestName we can use TestComponents to find out what is to be measured.

Then we need somewhere to hold the actual measurements. This will depend on the component and the sample.

Table: Results
    SampleID
    ComponentName
    MeasuredValue
    MeasuredAtDateTime

I’m assuming that a particular component will only be measured once even if it occurs in two tests. For example, if Test A consists of blood sugar and vitamin A, and Test B consists of blood sugar and iron level, and both Test A and Test B were ordered, then blood sugar would only be measured once from each sample.

A way to check you model is to list the questions you want to ask of it. Can your model answer those questions fully and unambiguously? If not you need to talk to your users about how things work in the real world and add this new understanding to the model.

Finally, don’t forget normalisation. As you add more attributes into the model ensure you are adding them to the correct table. You may have to add new tables and revise the relationships between existing tables. Don’t worry, this is normal in database design.

I hope I’ve given you some good pointers on how to proceed. Concentrate on what people do and what they do it to. This will be a good start. Lab software is field in itself and can be complicated. There are a number of commercial packages in existance.