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.
TestId (PK) - INT TestName - varchar(100) Status - bit (active/inactive) ..Other Columns
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
ReportId (PK) BigInt/Int TestId (FK) BigInt/Int PersonId (FK) -- as stated in question Status - bit (0/1) ..Other Columns like date,etc
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
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
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
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.
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.