Database tables design question

[Fuma]

So I have my main with a couple of fields that basically have the same data.
I'll just use a stupid eample below:

Main Table

MaindID
Field1
Field2
Field3
[B]EarTest
EyeTest
MouthTest
NoseTest[/B]

The above test fields have the same result sets
TestLookUp Table

[U]Test[/U]
Once
Twice
3 Times

Should I create a look up table (TestLookUp) for each field (EarTest, EyeTest, MouthTest, NoseTest) or can I create one look up table and link each field to one field on the TestLookUp table?

I know this is a design issue, and I want to avoid unnecessary problems.

 

[musacj]

Hi Fuma, I would create one lookup table, say called TestType with the ff definition
TestTypeID (pk), TestTypeDescr (varchar) {for 'Nose Test', 'Ear Test', 'Mouth Test', 'Nose Test'}, etc.
Will then have a table say TestCount with MainID from the main table as a foregin key plus other fields such as NumOccurence (e.g, Once, Twice, 3 times).

 

[Fuma]

Tx musacj. I know it was not a good example, but I will see if that will work for my particular problem.
I have a feeling they will need a field for each test (for this example).

 

[zippy]

Be aware that too many joins negativly affect performance.

I would only have 1 table. If in the future you need to add a value that is only applicable to one of the fields, you can add a field to your table holding the lookup values to indicate whether a value is for a specific field only.

Many systems have a single reference data table which contains lookup values for different tables. So your reference data table could be something like:

ID
VALUE
DOMAIN
contents of table:

ID VALUE DOMAIN
1 Once ALL
2 Twice ALL
3 3 Times ALL
4 9 Times EyeTest


EDIT: You can extend this further by have 3 value columns. 1 to hold a numeric, 1 for a character and 1 for a date. This same table can then be used for ANY lookup. The advantage of this is that all your lookups are in a single place, not dotted all over the place.