Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

One-to-one Relationship Vs. Single Table 2

Status
Not open for further replies.

swaybright

Technical User
Jun 25, 2003
156
US
I wanted to get people's perspective on the issue of when to use a one-to-one relationship between two tables and when to make a single table.

For example, I am in the middle of designing a db; I have a table (tblResults) that stores experimental results on a particular material. One set of experiments are done all the time and so the resulting data is in the table.
Another set of experiments are done rarely. Should I put these results in tblResults or create a new table?

If anyone has some practical guidelines on the basic issue, Please post.

Thanks,

Shane
 
There is no hard and fast rule. I would be inclined to have a separate table. The idea about databases is that they should reflect the real world. This makes them easy to use. Relational databases were designed to make things easy to use and forget all about efficiency etc. If you see this as a separate real world situation then reflect that in the entity-relationship model.

 
A number of problems can occur with a single table:

1. You define x number of columns to hold all of the different categories of data needed in each record, then later on a new category is needed. With a separate table related to the master table you can have a column that identifies the category (so you can have whatever number of categories are needed for each specific data row in the master table) and a second column that contains the data for that category.

2. You also can end up with a larger database because there may be many records with columns that need no data. Of course, wasted space is not as critical these days, although it still could slow things down a bit.

3. Some types of data extraction can actually be more difficult with the single table. Using the layout described in 1 above, if you want all values for the category "Top", you can easily select all records in the second table where the category column contains "Top" and get the result. Otherwise you must first determine which column in a single table reflects the Top category and then limit the records to only those containing values.

Your example seems to be one where a second table might contain a column for the experiment identification and a second for the result category and a third for the value (or a third for the value and a fourth for the text/notes or whatever).
 
Thanks for the input, guys. This is really good stuff. I was leaning toward the multiple table solution myself, but I wanted to tap into some unbiased minds. I had decided to do the multiple tables based only on BSman's number two reason, which as he pointed out is not really a good critera. I think his third reason is the most important from a relational db viewpoint. It facilitates queries.

One additional note--referencial integrity. It cannot be applied to a one-to-one relationship unless there is always a record in both (all) tables. Which, in my example, will not be the case; however, it is important that there be no orphaned records. Thus, the solution, for me, was to make the relationship(s) one-to-many with the knowledge that there will be only one record in the child table(s). Then referencial integrity can be enforced.

If anyone else has any input, particularly if there is someone who favors the single table approach, please chime in.

Thanks,
Shane
 
"One additional note--referencial integrity. It cannot be applied to a one-to-one relationship unless there is always a record in both (all) tables."

I don't think that is entirely true. Referential integrity says you can only have a record in Table 1 if its foreign key to Table 2 is the same as an in-key in Table 2 or is null. So you add your main table results with no difficulty. When you come to add your rare table record you normally add it with the key of an existing main table record. If for some reason you wanted to add the rare table record first then you would have a complication as its foreign key is also its key and thus can't be null. You'd have to have a duplicate field to be the foreign key in that kind of unusual scenario. Anyway that doesn't apply in your case.

To put it another way you can have a department without any employees - you just can't have any employees without a department, so in your case you can have a main record without any rare entry but not the other way around. It would be pointless to say you can only have none or one of both because a) you are gaining nothing over having one table instead of two, and b) you couldn't physically add them beacuse whichever one you tried to add first, the DBMS would reject because the other one wasn't present.

 
BNPMike,

I made that statement from experience. I have not found a way to create a formal one-to-one relationship with referencial integrity where only one table will have data. Remember that for a formal one-to-one, primary keys are in the join! Thus there is no foreign key. And because the join is between primary keys, Access makes both tables parents and requires that if a record exists in one, a record must exist in the other (not quite the right terminology, but I hope you follow). And, you are right, you cannot add data to either table!!
What I ended up doing is as you described, which applies to one to many relationships... One department--Many employees. (There can be department(s) with only one employee)
I would love to be able to create the one-to-one with ref. integrity, so if you can figure out it can be done in practice, I'm all ears.

Shane
 
Oops! My apologies, BNPMike. Discount my last post. You can create a one-to-one with ref integrity if there are only two tables involved. The situations I have dealt with involve several one-to-ones on the same primary table. In those cases, ref integrity can be established, but cascade update cannot be invoked.
Forgive my ignorance; thanks for calling me on that.

Shane
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top