Tables and List Boxes

[vguna]

Hi,

I am trying to develop a database with a table that has patient information in it. This table should hold the following:

1) Patient number
2)Name
3) Age
4)Gender
5)Allergies

The patient can have more than one allergy ( penicillin, seafood, sulfa drugs, latex, no allergy). I am having trouble designing this particular field. Now the user of the database keys in the information and the idea is to give him a list box with the allergies listed and have him click whatever is applicable. In a such a case would I have to create separate variables for each allergy and store a yes or no value in them depending on whether the user picks that allergy from the list or not?

In other words, instead of having one field named allergies should i have 5 fields: penicillin, seafood, sulfa drugs, latex, no allergy with field type yes/no?

I would appreciate it if someone can help me with this!

Thanks,
Vidya

 

[Remou]

You should have an allergies table:
tblAllergies
PatientNo
Allergy
Fields

 

[Golom]

You should have a different table like this
[tt]
tblAlergies
PatientNumber Allergy
1 Seafood
1 Peanuts
2 Cheap Booze
etc.
[/tt]
The reason for that is that multiple allergy fields on the main table will have some problems ... among them
[li]You will need to modify your table structure to add more[/li]
[li]Answering questions like "Who has a Seafood Allergy?" involves searching all the allergy fields and (see the above) you may have changed how many there are so this query has to change too.[/li]
[li]Modifying a record to remove an allergy is non-trivial.[/li]
[li]You have a whole bunch of unused (NULL) fields in your database because it's designed to hold the maximum possible amount of data ... not just the data you actually have.[/li]

In database design lingo, that structure is called an un-normalized table and is to be avoided.

 

[lespaul]

read the link below for more information on normalization.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[vguna]

Hi,

If I have a separate table tblAllergies then I should have a patientAllergies table in the relationship because the two tables will be many to many. In that case how would I construct the form. In other words, what would the recordsource of the form be?

Thanks,
Vidya

 

[Remou]

You can have an Allergies subform, related on Patient ID.

 

[lespaul]

you can also make a query the record source of the form.

Leslie