Table Re-Design 4

[jean2002]

Hello, I’ve just had a major thought about the structure of my database, and I am considering doing a major revamp of it. Can someone please give me his or her opinion?

The scenario is as follows: I have a database that contains various Persons, named tblPersons. Each person has been taken/measured for bodily measurements. Over the years, there has been 4 different types of measurement systems, each with about 20 different measurements. Each measurement system’s measurement types differ, except for a few common ones like BodyHeight, SittingHeight etc. There is also a possibility that each person has been measured by more than one measurement system.

I created 4 tables: one for each measurement system e.g. tblMeas1, tblMeas2, tblMeas3, tblMeas4 – each has about 20 fields. Then, seeing as each person can only appear once in each table, I created a One(Person) – to – One(MeasurementSytem) relationship between each of the Measurements table and the Persons table. It is more often that one person is only in one of the Measurements table, but that can always change.

I am still not 100% convinced that this is the right structure. I made a union query to bring all the 4 tables together for all the Persons, but the problem comes into play when there is a person with more that one measurement system. I was thinking of setting up one big measurements Table, that contains all 4 measurement systems under fields like Meas1_Bodyheight, Meas2_Bodyheight, … , …, Meas1_Sittingheight, Meas2_Sittingheight etc. It would then still be a one – to – one relationship though (with 80 fields!).

I know that table design is very important, and have not come across many examples of one – to –one relationships, so can someone please give me some advice?

Thank you for your time and attention!

 

[jean2002]

Hi all,

I tried the SQL query (as Bob recommended) on my existing table structure, i.e. one tblPeople and 4 tblMeasurementsX. It works quite well I must say. I can probably then base my forms on this query, and write some code that will always look for the latest measurement system used on a person.

Bye for now!

 

[BSman]

I'm very late to this discussion, but I thought I'd thrown in my 2 cents (or less). I agree with the basic concept of lespaul's suggestion. One table for the list of people, a lookup for measurement system, a lookup for what was measured, a lookup table that has one row for each valid combination of measurement system and what was measured, plus a conversion factor (if that is possible) and an indicator of what combinations are still valid (to avoid choosing "old" combinations when entering new data), and the table that ties it all together:
PeopleID, valid combination ID, and the associated date: date, measured value.

It's probably not realistically possible, but you can also include a multiplier (factor) that could be used to convert measurements of the same thing (example: height) to figures that could be directly compared, I would be sure to include that. A simple example would be if one system used pounds for weight and the other measurement system used kilograms.

By having all of your measured data in one table you have one place to find all of the data and through selection criteria and grouping (for totals) you can look at one measurement system, or one person measured more than once, or a specific thing that was measured.

 

[SkipVought]

BSman,

I don't think you want a conversion factor.

If data is being collected from a variety of meauring systems, then you want to store the UNIT OF MEASURE.

For instance, linear values could be recorded in milimeters, centimeters, meters, inches, feet, yards. In these cases, the UNIT OF MEASURE is essential to converting to WHATEVER system, in WHATEVER units is preferred for analysis and reporting.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[BSman]

Yes, unit of measure would also be a good thing to store. The conversion factor I suggested would be to one standard measurement method for that specific item so that ratios and relative changes could be calculated accurately. So the conversion factor could take into account something like, as a lousy but illustrative example, one way of measuring height that is always 10% lower than a person's full height. As a result, measurements with that system/method should be multiplied by 1.1 to compare with measurements with the "full height" method when the actual measurement in both cases is in inches.

If conversion factors are useful for the type of example I mention above, then it might be appropriate to also include a conversion table for the well defined conversions (inches to millimeters and the like).

 

[SkipVought]

OK, but that sounds more like a

Correction Factor rather than a

Conversion Factor.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244