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!

 

[scriverb]

hello Jean: 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.

Good luck with your project and post back if you have more questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SkipVought]

jean2002,

The reason that Relational Databases are called Relational is NOT that tables get joined (related) but that each TABLE is a distinct RELATION (the terms could be used interchangeably).

Hmmmmmmm. tblMeas1, tblMeas2, tblMeas3, tblMeas4???

Ahaaaaaaaaa!!!

These ALL are RELATED to measurements!

As you are aware, there is no ONE correct answer. In fact, there could be a number of sound options, each one with pros & cons depending on the perspective of your evaluation -- storage, maintenance, performance etc.

Some things to consider in your redesign:

Rather than "Meas1_Bodyheight, Meas2_Bodyheight" (again using the SAME kind of RELATION approch ot columns) since these are ALL body height measurements...
[tt]
...
MeasNum
BodyHeight
SittingHeight
...
[/tt]
Then I could have ONE or ONE HNDRED measurements per person. I don't know how diverse the UNCOMMON measurements are, including the NUMBER of UNCOMMON measurements in each case, but there are ways to use common columns that are defined by the CONTEXT of the Measurement Set. The Measurement Set could & should be defined in a parameters table if you go this route. By Measurement Set, I mean the set of measurements that are related, analagous to tblMeas1, tblMeas2, tblMeas3, tblMeas4. This table would define each set and would be used to interpret the table results.

Hope this helps

Skip,

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

 

[jean2002]

Hi Bob,

I am trying out your suggestion, but Access tells me something when I execute the query. Like "SQL can't execute since there are multiple Join-types. please make queries seperate blablabla" .

Here is what I did. Set Join type of each 1-to-1 relation to Option 2 (in "Relationships -> Join type"). Then in a new query, add all fields from all tables.
Then this error message comes. As an example, I took 4 fields from each table in this query and here it is in SQL:

SELECT tblPersons.PersonID, tblPersons.Surname, tblPersons.Name, tblPersons.Department, tblMeas1.PersonID, tblMeas1.BodyHeight, tblMeas1.BeckenB, tblMeas1.BrustkorbB, tblMeas2.PersonID, tblMeas2.BodyHeight, tblMeas2.BodyHeightwShoes, tblMeas2.SittingHeight, tblMeas3.PersonID, tblMeas3.BodyHeight, tblMeas3.SittingHeight, tblMeas3.KneeLength, tblMeas4.PersonID, tblMeas4.BodyHeight, tblMeas4.Eyeheight, tblMeas4.ReachLength
FROM tblPersons LEFT JOIN (((tblMeas1 INNER JOIN tblMeas2 ON tblMeas1.PersonID = tblMeas2.PersonID) INNER JOIN tblMeas3 ON tblMeas1.PersonID = tblMeas3.PersonID) INNER JOIN tblMeas4 ON tblMeas1.PersonID = tblMeas4.PersonID) ON (tblPersons.PersonID = tblMeas4.PersonID) AND (tblPersons.PersonID = tblMeas3.PersonID) AND (tblPersons.PersonID = tblMeas2.PersonID) AND (tblPersons.PersonID = tblMeas1.PersonID);

I am working on an Access project for the first time, have a thick reference book which just sometimes doesn't explain exactly what I need, so your help is much appreciated.

 

[jean2002]

Skip,

OK, so you say I COULD set up a table which will hold fields that are common to all measurement types? Yip, I considered that too already, but if someone is in two measurement system tables, I have to keep e.g. the old body height measurement also (it's different due to various factors e.g. accuracy, posture etc.).

Phew...i'm getting really confused. I think i must go back to the drawing board.

Thank you anyway 4 that!

 

[SkipVought]

[tt]
ID MeasNbr MeasTyp BodyHgt SitHgt
Sam 1 1 72.0 36.0
Sam 2 1 72.5 35.2
Sam 3 2 68.8 33.2
[/tt]
Am I missing something?

Skip,

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

 

[lespaul]

Check out 'The Fundamentals of Relational Database Design'.

Skip, is right. You will get different advice from different people. I personally wouldn't do as Bob suggested and leave the tblMeas1, 2, etc.

If I had to design something like this I would have:

tblPerson
PersonID
Name
address
(anything else about the person)

tblMeasurements
MeasurementID
MeasurmentDesc

(ID = 1 Desc = Body Height; ID = 2 Desc = Sitting Height)
and list ALL the Measurement Types that you take in this table.

tblSystems
SystemID
SystemDesc
(this is the table that you would describe this:

4 different types of measurement systems

tblSystemMeasurements
SystemID
MeasurementID

(this table is to assign which measurements go with which systems - take your existing tblMeas1; the fields in this table should have a record in tblMeasurements; take the SYSTEMID number of tblMeas1 and each MeasurementID number that corresponds to the field name in tblMeas1)

for instance

tblMeas1.BodyHeight
tblMeas1.SittingHeight

will become:

tblMeasurements
MeasurementID MeasurementDesc
1 Body Height
2 Sitting Height
3 Blood Pressure

tblSystems
SystemID SystemDesc
1 Measurement1 System
2 Measurement2 System
3 Measurement3 System
4 Measurement4 System

tblSystemMeasurements
SystemID MeasurementID
1 1
1 2
2 1
2 2
3 2
3 3
4 1

now you can add as many systems and measurements you want without ever having to change the design.

Now, the people are assigned to the systems (at least one maybe more)

you would need

tblPeopleSystems
PeopleSystemID (I add this to make the people measurements easier to relate to a specific person's system assignment)
PeopleID
SystemID

(same theory as above)

then you would need to track the measurements:

tblPeopleMeasurements
PeopleMeasurementID
PeopleSystemsID
MeasurementID
MeasurementData

Now, this is in no way complete, but it should give you some ideas on how to normalize your database. Be sure to read the link above, it is a must read for any database developer!

HTH



Leslie

 

[SkipVought]

Leslie,

I'm with you. Thanx for including that ESSENTIAL link!

Skip,

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

 

[scriverb]

Try the following replacement for your FROM clause in your query:

FROM (((tblPersons LEFT JOIN (tblMeas1 tblPersons.PersonID = tblMeas1.PersonID) LEFT JOIN (tblMeas2 tblPersons.PersonID = tblMeas2.PersonID) LEFT JOIN (tblMeas3 tblPersons.PersonID = tblMeas3.PersonID) LEFT JOIN tblMeas4 tblPersons.PersonID = tblMeas4.PersonID

As for the table design both SkipVought and lespaul make good points as to relational table design. The decision as to how far to split your tables down depends on what you are going to do with the data and the diversity of the measurement types and values. 20 fields per measurement system and only a few being common it is not unhead of to leave them all in the same table, especially if a person with a record for measurement system will have all of the fields filled in. Are you going to be analyzing them on the same line of report one against the other? Also, the data entry capabilities for a system like you describe should be considered. Does it make sense to enter the measurement data for a system all at once from your paper records?

You see in an accounting or transaction type system it surely makes sense to break down the individual fields into individual records with pointers to the sytem and measure type. But this does not seem to be the case. I am looking at your description of the system and trying to envision what you are going to do with these records of data. Is your comparison going to be across person records? Are you going to be grouping persons by ranges of the body measurements within the system. Sometimes it is better to have these records and their fields listed together to accomplish this.

I am not saying that what I have suggested is right or wrong for your application or the Skip or lespaul are correct or not either. I am saying that table design should try to follow the relational structure but only as low as the output and useage that are required. Sometimes it is better to stop the drilling down before its lowest level. Your description sounded like that to me.

This should spur on discussion here but that is good because it will help you with your system.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[SkipVought]

When you stated

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.

what is to say that in the future, someone will come up with a FIFTH Measurement system?

Quick 'n' dirty: I might use MULTPLE SIMILAR RELATIONS especially if I inherited something.

But you ALSO stated...

I’ve just had a major thought about the structure of my database, and I am considering doing a major revamp of it.

This decision is a watershed event. Gather sound fundamental information. Evaluate the alternatives -- all the pros and cons that you can conceive. It can become not only a watershed event in terms of this application, but ALSO in your own understanding and skill relating to data organization and information retrieval.

Skip,

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

 

[willir]

Jean

I am going to have to side with Leslie and Skip on this issue. Having the relationship as a one-to-one (1:1) may be correct decision, but I do not really see justification for having several 1:1 relationships with the information provided.

The fact that you are playing with Union queries or Left joins supports this.

I suspect you really have a one-to-may relationship, one person has many measurements. If you really want to get down to brass tacks, one measurement type has many person making this a many-to-many relationship where your table used for tracking measurements is really the joiner or intermediary table.

The real decision maker on the design is how do you plan to analyze the data. The design that works best for you should be the easiest one for retrieving the data and provide the information you require.

I suspect Leslie's post gives you a much simplier solution than having several 1:1 tables.

Now that there is a bit more info, ponder this logic. Right now, you have 20+ fields for each measurement type -- this makes the inclusion of each specific measurement for each measurement type awkward when you design a table. ...I think this is where a M:M design may shine.

First, pasting Leslie's design...

tblPerson
PersonID - primary key
Name
address
...etc

tblSystems
SystemCode - primary key
SystemDesc


tblMeasurements
MeasurementCode - primary key
SystemCode - foreign key to system type
MeasurementDesc
UnitType - inches / cm / lbs / kg / angle / time

I changed ID for CODE since it may be easier to use a short simple, meaningful text description for the key instead of a number. This is just a personal preference and does not change the real "meat" or meaning.

And I made the relationship between "System" and "Measurement" a 1:M - each system has many measurements (this is your 20+ fields).

...Now for the joiner table.

I feel you have a M:M relationship between person and measurement.

tblStatistics
PersonID - foreign key to person table
MeasurementCode - foreign key to measurement table
SystemCode - see discussion
Statistic - record measurement here

The primary key is PersonID + MeasurementCode.

I included the SystemCode in the above -- this breaks rule of normalization -- since you know the MeasurementCode, you can grab the SystemCode, but this means that every time you query the statistics, you have to include the measurement and the system table as well as the person table - four tables.

By using meaningful text codes and including the system table, your queries can mostly just hit the statistic table and the person table - two tables. (If you have more than 4 systems and ~20 measurement per system, then I would be more inclined to use ID number instead.)

Data would look like...
(I have very little idea on the different "systems" - example, why you measure body height four times, so I will use very generic "system codes")

[tt]
Person SystemCode MeasurementCode Statistic
1 SYS1 BodyHt1 5.7
1 SYS1 BckenB 123
1 SYS1 BrstkrbB 456
1 SYS2 BodyHt2 5.75
1 SYS2 BdyHtSh 5.8
1 SYS2 SitHt2 48
1 SYS3 BodyHt3 5.7
1 SYS3 SitHt3 49
2 SYS1 BodyHt1 5.7
2 SYS1 BckenB 123
2 SYS1 BrstkrbB 456
2 SYS2 BodyHt2 5.75
2 SYS2 BdyHtSh 5.8
2 SYS2 SitHt2 48
2 SYS3 BodyHt3 5.7
2 SYS3 SitHt3 49
[/tt]


Now I am going to go out on a limb....
(I can hear the branch snapping as I type ;-) )
Let's say your "systems" are actually "experiements" where a system is a regime such as diet, or condition - measurements in AM / PM or before and after excercise...

The tblStatistics table would be just about the same with one glaring difference. You now don't have 20 different measurements for each system or 20 x 4 = 80, you have 20 measurements. Instead of BodyHt1, BodyHt2, BodyHt3 and BodyHt4, you just have BodyHt.

If I am correct, by treating each measurement independent of the system, ie. BodyHt instead of BodyHt1, your statistical analysis will be much easier.

If I am correct, then the primary key for the tblStatistics becomes PersonID + SystemCode + MeasurementCode.

Well, did my branch hold, or did I fall thousands of feet to a terrifying end too horrible to imagine?

One last issue. If you are taking multiple measurements per system + measurement, i.e., measure a person's height 3 times under System1, then you need to tweak the design
a bit
- Either add a "StatDate" field where the StatDate is the date or date + time when the measurement was made, and the primary key needs to include the StatDate field.
- Or, use a StatisticsID field as the primary key.

Richard

 

[jean2002]

Thank you all for such enthusiastic responses, you all deserve 5 stars!

After reading the doc on Relational Design, it has just re-inforced my understanding of the basics.

I will try out the 2 approaches as suggested by lespaul and willir. I should perhaps put some more info of my business environment in here, that I missed in the last post :

The systems that we have, are various methods of measuring a person (from most recent to least) - e.g.
(1)using a laserscanner,
(2)a video-computer grid measurement
(3)right back to using physical methods, like having a person stand next to a big ruler and noting the measurement. Therefore, there are slight differences for one person in e.g. BodyHeight if he/she has been measured with two systems, e.g. due to accuracy, posture, time of day ...

We have a spreadsheet with over 500 people, and over the years some of them have been measured only with one method, others have been measured with the later technology.

The important thing to note though, is that the later technology used, will be mostly the measurements that will be searched on. I.E. if someone has been measured with two systems, and we do a search, then get the values from the latest measurement system.

The client would like to keep all the older data though, as quite some effort went into this over the last 10 years. It is their "baby".

Ultimately, I would mostly want to do searches on on all persons in the database, using the different criteria such as BodyHeight etc. There will also (a bit later in this project when I sort this out!) be comparisons made to another table, to see which percentile of the population group this person falls into based on their bodily dimensions(Ergonomics). E.g. someone in the 95th percentile - this means in a group of 100 people there is only 5 people that are bigger. Then, I could select e.g. 5 suitable sized people to take part in tests.

I am now realising, yes that no one answer is correct. But I will experiment a bit with your suggestions. A couple of questions again though:

lespaul,

Now, the people are assigned to the systems (at least one maybe more)

you would need

tblPeopleSystems
PeopleSystemID (I add this to make the people measurements easier to relate to a specific person's system assignment)
PeopleID
SystemID

(same theory as above)

then you would need to track the measurements:

tblPeopleMeasurements
PeopleMeasurementID
PeopleSystemsID
MeasurementID
MeasurementData

In tblPeopleSystems, is PeopleID a foreign key to PersonID in tblPersons? Just wanna make sure....

willir,
It's mostly the case that a person has just been measured once with a singular system. But there are times when a later technology was used, and then we must use these dimensions. But of course, keep the old measurements for archives.

Bob, I tried that change, but it came with a SQL-syntax error in the Join, where it pointed to the first full-stop in

FROM (((tblPersons LEFT JOIN (tblMeas1 tblPersons.PersonID = tblMeas1.PersonID)

I sorta guess you guys have not seen a problem like this before, I was really impressed by the response to this thread when I saw it this morning. Thanks again!

 

[scriverb]

Since that code is mine I might as well speak up and make the fix:

FROM (((tblPersons LEFT JOIN tblMeas1 ON tblPersons.PersonID = tblMeas1.PersonID) LEFT JOIN tblMeas2 ON tblPersons.PersonID = tblMeas2.PersonID) LEFT JOIN tblMeas3 ON tblPersons.PersonID = tblMeas3.PersonID) LEFT JOIN tblMeas4 ON tblPersons.PersonID = tblMeas4.PersonID

Sorry about that. This has been an interested thread and problem. Some very good ideas here on normalization of tables and relational database design. Although my point did not get across to others and I surely understand the table designs posted here and their justifications which I have used many times in the past, I would like to just say that keeping in mind those design techniques you must also temper the breaking down of the data only to the level that is necessary for the particilar process. Other factors do come into play that should be considered as I mentioned earlier. I personall think that with statistics as you have described a single table linked to the person with the possibility of 4 records, one for each system, may be as far as you need to normalize your data. Without actually seeing the data and the analysis and reports you are going to develope a single record with the stats for the measurement system for an individual may suffice and allow for a better presentation of the data in forms and reports. Your analysis can still be accomplished easily in queries as you have described them here.

An example of over normalization would be a Family(name, address, income, etc) with a many table for Members each linked back to the Family record. Each of these would be identified by a code to designate Mother, Father, Brother, Sister etc. It is here that you have to make the choice about the info for each family member. Should the fields which are all relatively the same for each member be normalized and broken down into indivual records each linked to a Family/Member record or should they just be detailed on this record. Fields like DOB, Height, Weight, Sex, School, Grade, hobbies, etc. We could put them all in a seperate table called MemberDetails with each having FK links back to the Members record and each being identified by a Descriptive code along with the information.

Thinking of the above example along with your statistics my first inclination is that when the user enters or reviews a Person and then wants to see the stats for the latest measurement system they want to see them layed out in a visual way that they can get the mental picture of the individuals body. If the data is broken down into individual records of stats more than likely the way to view it is in a subform that you can scroll through the records. Personally I think you want to see all the fields together ina logical body makeup design. Also, when inputing this data it is much easier to enter the data on a signle record with the fields in the order of logical entry.

I know I am going to stir up more discussion here but that is my 2cents worth. I am certainly not say that the designs discussed here are wrong. They are absolutely correct in a theory and conceptual sense. But, I have seen too many databases broken down into way too finite of record detail without considering the other factors that I have pointed out and they become a nightmare for the users.

Good luck and thanks for the star even if I am the black sheep in this thread.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[willir]

Hi Again Bob

I agree with you that common sense needs to be included in a design, and that normalization to the nth degree results in one having to dig too deep to grab the data. ...I did get your point, and understand your reasoning. I also respect your other postings, and tip-master kudos -- you are hot stuff.

However, Jean confirmed my suspicions that this database design was to analyze differences - in this case, differences in measuring "systems" - which takes us to a statical analysis, or analysis of varaince solution.

I hate to get technical, but this type of anlysis looks at the variance of the population (500+ observations) and then uses variance / standard deviation of the populationt to compare the means (averages) of each method to see if the means are statistically different from each other. To do this, it seems to make sense to use one table to capture the data, not one table for each method - otherwise calculating the population variance and standard deviation becomes problematic (but still do-able) when four different tables.

Richard

 

[scriverb]

Richard, thanks for the kudo's especially from you. You do well yourself here at TT and we all appreciate it.

I agree with you that one table including all four systems should be used. What I was trying to make the point was not to go farther than that where each field was a seperate record in another table with FK's pointing back to the system used. This is breaking it down way too much for this situation i think.

I think we are on the same page here. Yes, my initial posting should probably have recommended a single table with the possibility of 4 records, one for each measurement system. That design certainly is recommended here. But to break those tables down to individual records for each measurement field I believe is counter productive in this instance.

I hope this clears up my position on this subject. I hope that Jean2002 has benefited from the discussions from all of us here.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[willir]

Late night huh Bob. Good thing you probably have tomorrow off.

I am glad we agree we seem to agree with the table thing. I was basing my comments on your SELECT statement referencing multiple tables.

And thanks for the complement -- I guess I should say that I learned a lot from you, and have given you a few of your well deserved stars.

Richard

 

[scriverb]

Yea, I see that. Sometimes you are answering an immediate problem like why the FROM statement isn't working which is what the code for the query was about and at the same time recommending overall something different.

Jean2002, was asking why the join wasn't working and in trying to get her fixed up there you are not really pointing them in the correct overall direction that you want to. So, I can see why it may have been a little confusing.

Yes, I do have tomorrow off and will really enjoy the time with my family. Have a good night as i am out of here.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jean2002]

I definitely have benefited from this interesting discussion.

I am still unsure about this quote from Bob though:
(I know, you guys deserve a break now and then )

Yes, my initial posting should probably have recommended a single table with the possibility of 4 records, one for each measurement system. That design certainly is recommended here. But to break those tables down to individual records for each measurement field I believe is counter productive in this instance.

So, I played around with the suggestions from willir and lespaul, in particular using a many-to-many relationship, and using a tblStatistics to record measurement data.
Bob, is this what you mean by oversimplifying the table structure, i.e. "break those tables down to individual records for each measurement field"?

I also understand your last comment "a single table with the possibility of 4 records, one for each measurement system" as follows: Having one table that holds all measurement data as follows:

PersonID MeasMethod BodyHeight SittingHeight .... KneeLength

001 M1 1899 899 .... 645
001 M2 1898 897 .... NULL

This means that if PersonID = 001 has two measurement methods, and M2 does not have KneeLength as one of it's measurements. Therefore the one table solution will contain a Null value in the one field. Am I hitting the nail on the head here or missing miserably?

From willir:

However, Jean confirmed my suspicions that this database design was to analyze differences - in this case, differences in measuring "systems" - which takes us to a statical analysis, or analysis of varaince solution.

Once all this data is organised, yes I will have to compare the values of an individual to set values (maybe a comparison table) to find out if they are e.g. big or small for their population group etc. But I reckon that could justify a new thread, hehe!

If not, I'll always come back here for the great help I have got so far!

B.T.W. I'm a he, not a she!

Regards,

Jean

 

[scriverb]

Hello Jean. Sorry about the He/She thing. Yes, you have read the posting correctly concerning the single table with a 4 records possible for each measurement system. I would not break this table structure down to the individual field level as was suggested by others. The table structure that you detailed above is what I would suggest. Here you can make comparisons across the table records from one field to another. And yes, if there is a field for someone that has a null value you can handle that with code when analyzing the statistics.

We all will be watching the threads for a similar posting as you progress with your design. If you start one please make a final posting here with the new thread# so we can make sure to take a look.

Good luck. BTW, if you don't mind where are you located. I just like to keep track of TT members and their locations.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[jean2002]

Hi Bob,

That clears it up for me now. Thanks! No worries about the he/she thing.

I am currently working on a project in Germany, but I come from South Africa. Will be here till end of the year, then going back for a holiday.

Regards,

Jean