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!
:)

Doing a 1 to 1 relationship to a table which will then contain 60 Null values for a record is exactly the reason why you want to split it. The whole goal is to "store" as few Nulls as possible.

How about this?! You say you have 4 systems each about 20 measurements...
Well make 1 table with the max number of columns in 1 measurement. Then add 1 field to signify the type.
Then you can make a 1-1(possibly many) between those 2 tables no problems. If so desired you can then report on the measurement type... Or change captions of labels and stuff...

Greetz

I'd keep one measurement type in the table and make some conversion functions which, when entered, will convert to the appropriate measurement.

AFP,

Without knowing everything about your business needs, the following might do the trick -- based roughly on what you wrote.

TablePeople
. PersonID (Primary Key)
. FirstName
. MiddleName
. LastName
Etc.

TableMeasureSystems
. MeasureSysID (PK)
. Description

TableMeasurements
. PersonID (Composite Primary Key)
. MeasureSysID (Composite Primary Key)
. MeasureDate (Composite Primary Key)
. BodyHeight
. BodyWeight
. BodyFat
. SiliconQuotient
. AnkleWidth
Etc.

But here's the question on everyone's mind: Why are you the AntiFashionPimp?

Regards,
Tim

Thanks guys, for your feedback.

I’d like to add a few comments though. The measurements that I have might be similar in some ways, like BodyHeight etc., BUT I have to also keep the other measurements which are not common across all methods, e.g UpperArmCircumference in Meas1 vs. ElbowCircumference in Meas2. These are very small details that I must consider. I optimally have to also keep the old measurements, e.g. if BodyHeight was measured with Meas1, and this person now gets measured again with Meas2; there is now a new BodyHeight from Meas2. It gets quite confusing I know... :confused:

Doing a 1 to 1 relationship to a table which will then contain 60 Null values for a record is exactly the reason why you want to split it. The whole goal is to "store" as few Nulls as possible.

How about this?! You say you have 4 systems each about 20 measurements...
Well make 1 table with the max number of columns in 1 measurement. Then add 1 field to signify the type.
Then you can make a 1-1(possibly many) between those 2 tables no problems. If so desired you can then report on the measurement type... Or change captions of labels and stuff...

It would be good to have this solution, but I cannot combine everything in one table. This is because sometimes a person has a past measurement method and the latest too. I then have to keep all the past measurements too, as said earlier. The main point is that when the data is displayed in a form, the latest measurements for each person will always be shown, and the older measurements (if any) will be kept for archiving purposes and comparing differences between the old and new methods.

I'd keep one measurement type in the table and make some conversion functions which, when entered, will convert to the appropriate measurement.
It would be difficult to make a conversion, as e.g. I cannot get NeckLength =BodyHeight – ShoulderHeight – HeadHeight. These are bodily measurements (taken by an extremely accurate Laserscanner) and unfortunately I must work with the data as I get it from source. Therefore no conversions, Sorry!

Tim, as I said earlier in his post, it’s difficult having everything in one table. The purpose of this database is for keeping track of people who take part in tests for new automobiles, and we have to get the Ergonomics right.

Oh yeah about the name:
It’s not really a description of the kinda person I am. I was just one day wearing a t.shirt with a print that read “anti-fashion” and then at the same time I met a mate who greeted me “whats up, Pimp?” The combination I think is unique and easy to remember, so why not use it in forums…nothing to do with my general attitude towards clothing and ladies. ;)

Oh yes, I got this from another source, what do you think to this:

"The design that you have created is just fine. Your 1 to 1 relationships to your four tables is appropriate. Just make sure that the ForeignKey in each table is a index with no dups. This will not allow for a duplicate record for an single person.

It is not necessary to create a UNION query just create a multiple table query with left joins between the Persons table and the four measurements table. Select all fields from all of the tables and your will get a single row of data for each person with data from the tables in the columns if a record exists for that particular table. You can easily check for IsNull on the ForeignKey field for each table to see if there is data to consider.

The ability to create a form or a report with tblPersons as the RecordSource and having four subforms/subreports that will display the linked records makes for a nice design and easy in adding the individual records if necessary." Bob Scriver

Sounds OK to me...

Which is more or less what i was saying... or meant by Doing a 1 to 1 relationship to a table which will then contain 60 Null values for a record is exactly the reason why you want to split it. The whole goal is to "store" as few Nulls as possible.

The other idea is also more or less the same except you have 1 table instead of 4. Then in 1 field you would store 4 different values, for which you would change the lables as needed...

Greetz