Why have a one-to-one relationship? 2

[gusbrunston]

[tt]
In a recent thread, someone who knows much more than I do about Access and programming in general asked "If relationship between the tables is 1:1 why have seperate tables?"

Here's a real-life example (and the only one I can think of):

A property manager sends monthly reports to owners of the individual properties managed by the company. In a few instances, the owner wants the report sent to a designee, an attorney, trustee or guardian, etc. Since the name, address, and other descriptive fields for the designee are needed only for a relatively few records in "tblOwners" it seems an efficient use of space to add a "tblDesignee" as a "look up table", in a one-to-one relationship with "tblOwners".

I remain, subject to enlightenment, [tt] Gus Brunston - Access2000[/tt] Intermediate skills.

 

[KenReay]

Hi

I would say that was a good reason to have a 1:1 relationship.

I my experience there are not not many situations where they are needed, but there are some!

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

There are numerous situation where a significant amount of information is not used / available / changes more rapidly than other parts of the 'record'. One simple example is in banking account balance(s). The basic account info doesn't change often, however the balance may change several time per day. Why 'update' the address and account number to post a transaction? This same example also includes some 'security' capability, as the table with the account balance may have different access criteria then the name and address.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Craig0201]

Gus,

Your example is actually one to many.

A designee could actually receive many reports, each from a separate owner.

Logically, there is NEVER a need for 1:1. In SSADM (a UK system design technique) they are banned.

BUT they may be reasons to do this for speed, etc.....

One of the most important things i've learned lately is that controlled denormalisation does wonders for app speed!

However, as a general principle, your friend is 100% correct as anything 1:1 can actually be part of the same relation.

Craig

 

[danvlas]

Craig...never say NEVER

Here are two cases where you must employ 1:1 relationships:

1. You need to simulate a column level security. Actually, you set different levels of security to different tables, but having 1:1 relationship, you treat both tables as one set of data.

2. (not likely to happen, but...) if you need more than 255 fields in a table, you reach Access's limit, so you have to create another table with the missing fields...

And the list doesn't end here...


Daniel Vlas
Systems Consultant
danvlas@yahoo.com

 

[Craig0201]

Daniel,

Hence the clarifier Logically as in the Logical Design, not the Physical design. There are always reasons to ignore the logical. Just make sure they are good ones!

Craig

 

[RickSpr]

I want to emphasize the security advantage that MichaelRed mentioned. You might have an Employees table and a Salaries table in a 1:1 relationship, where Employees has unrestricted use but Salaries is available only to the HR group.

It's true you could logically accomplish this by controlling access to forms, queries, etc. that display different subsets of data, and in fact you'd still need to do this to prevent error messages about not having permission to view the HR data. But by separating the sensitive data into its own table, you can add Jet's checking at the most basic level. Depending on higher-level logic to protect the sensitive data exposes you to compomising this data through application design flaws or unanticipated back doors. The Jet checking is highly reliable.
Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Hasse]

Are there any experts/users with an opinion about this reason to use 1-1-relationships?

"Memo fields are said to be avoided as they might cause database corruption"

Example: a typical products-clients-orders database. Few clients ask for a receipt. The user of the order management application asks for some free text in the body of the letter. The rest is derived information. So I get a:
- tblOrders = or_IDorder + ...
- tblReceipts = rc_IDreceipt + rc_IDorder + rc_Text
with rc_Text = memo.
=> the receipt is a report based on several tables.

Besided the fact that few clients want receipts, the rumour about the memo field disadvantabe towards stability of the database was an important arument in advance of the 1-1-relation... Any idea whether it was a valid one?

Hans
ps merry christmas to everybody!

 

[MichaelRed]

'Validity' is similar to beauty - it is in the 'eyes of the beholder', so as long as it is 'Beautiful: to you it is "o.k." - but this is not the normalized approach. From my perspective, the "receipt" should exist without regard to the customer copy. It is an integral and vital part of the process and should be included as the seperate field(s) to produces the complete receipt. The 'customers' diesire (or lack thereof) is simply the criteria (and boolean field) to desigante wheather the receipt is printed (or an integer field to indicate the number of copies orinted).

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Darrylles]

Hi,

Trustee, Attorney, Guardian are all 'Contacts'. They all share common attributes (Title, Forename, Surname, and Address).

1:Many - whats the question?

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[Hasse]

Michael,

I see now that my question wasn't very clear: I wondered primarily about the validity of the 'memo fields might cause corruption' argument.
Nevertheless, thanks for your answer. I agree with you, except that I tend to consider the receipt, in normalisation terms, as an entity so that it still might 'deserve' an individual 1-1 related table as soon (!) as there need multiple 'properties' to be stored (e.g. print date, text, remarks).

and Darrylles,

In my opinion, your question isn't really about 1-1 <=> 1-many. I should advise you just 1 table:
tblContacts = type + title + forename + ...
with type = Trustee, Attorney, Guardian.

I hope it's of some help!

Hans

 

[DanJR]

There are 3 ways to map a supertype-subtype arrangement:

1. absorption
2. separation
3. partition

Craig, separation leads to a logical 1:1 (doesn't it?). Separation is where you create separate tables for each subtype and have a table for the common facts.

Cheers
Dan

 

[Hasse]

(I had better waited for DanJR's more expert like answer, which makes mine quite 'amateuristic'. So the forum manager is free to take my post(s) out. Just in case he doesn't,I'ld like to 'finish' the story as appropriate as possible...)

Dan,

=> am I right that the table (including ID ) described in my previous post only illustrates about the 'common' facts part and, depending on the solution, the 'unique' facts of the subtypes should be stored
1. absorption: all in the same table, where depending on the case, only the relevant facts fields will be entered;
2. separation: in a separate table (with only the fields relevant) for each subtype (tblTrustees, tblAttornees,...), where each table contains a referring key 1-1 linked to the 'common facts' table
3. partition: ...?

=> can you give some more info about option 3?

=> do you mind to give some short (dis)advantages?
E.g. absorption seems to me the easiest solution for a non-expert developer: easier forms & queries to gather all information, faster(?),...

Thanks,

Hans

 

[DanJR]

Hi Hasse,

My post may have sounded like a more 'expert' post, but this doesn't mean its by an 'expert' . Compared to those that have already posted in this thread, I am pretty much at the other end of the scale - an amature. My post was as much a question as it was a statement - simply stating that a logical 1:1 can exist(?).

In my opinion, its best to absorb subtypes back into the supertype table where possible. This is the simpliest method and avoids the need for joins to express a subtype (where subtypes include common facts). However, absorption leads to fields that only apply to same rows in the table and not to others. This leads to 'missing' values in these fields meaning that the field either doesn't apply or the value is infact missing. Also, if the subtype occurs infrequently, then there can be alot of null values for the subtype-specific fields. It may also become difficult to express validation on a table when subtypes are absorbed. However, any disadvantages of absoption can be mopped up by a bit of VBA.

I had two subtypes (Sample & Product) which are both Formulations (supertype), which I separated into two 1:1 relationships with tblFormulations. Both a Sample and Product contain a list of ingredients. The reason for separation was because 1) only a small number of the many attributes that Samples and Products contained were in common; 2) there were many 1:M relationships with Samples that were not relevant to Products; 3) I *thought* that the reporting and searching that I needed would be more efficient (run faster - the indexes would be more efficient due to the absence of null values(?)); 4) and the ratio between Samples and Products was estimated to be about 50:1.

I still don't know if I made the right decision, but all i know is current design works fine and that I had to make a decision!

However, I feel that the more important issues surrounding 1:1 relationships have already been discussed by whom i beleive are the true experts...

Cheers,
Dan.

PS Partition is where there is no common table ie the subtypes are exclusive and exhaustive.

 

[Hasse]

Thanks! - (and for what it's worth: I think I'ld have chosen the same solution) - greetings - Hasse

 

[KornGeek]

Just to add my 2 cents to this discussion, I often use one-to-one relationships when I have a table that is linked to another database and I want to add additional fields to the table only in my database.

For instance, I do a lot of development involving Palm handhelds. In these programs, I use temporary tables to HotSync data between the desktop and the handheld. When my customers want to keep track of more information on the desktop that doesn't need to be stored on the handheld, I have a second table with a one-to-one relationship. This way, only the necessary fields are stored on the palm (which has limited storage space), and through the magic of a query, the desktop database can treat it as one giant table with more fields.

 

[tomvdduin]

This is where I use one-to-one relationships: I have a table with information about human beings, separated in adults and children. I want to track much more information of adults than of children, though they also have a lot in common. I put the common information of children and adults in one table, and the extra information of adults in a new table with a one-to-one relationship...

 

[johnnymac43]

I couldn't resist buying into this one!

First question: If you were developing a system to record births and deaths would you have one or two main tables???

Some might choose one, but I suspect that not many would be prepared to wear the overheads of all those half-empty table entries, many of which would remain that way forever as many people do not die in the jurisdiction in which they were born.

Second question: If you were developing a system to record details of feedlot cattle, where 50% of the data relates to death but 90% of the user processes relate to life events, would you have one or two main tables???

I recently redesigned a system that had originally been done with one main table. One of the reasons it needed updating was that the processes run by 90% of the users on details of life events were bogged down by the masses of death data fields in the main table.

Yes! I now have a 1:1 relationship between life data and death data, and the system runs like a dream!

Just thought someone would like to know of this real world scenario.

Cheers

John

 

[Craig0201]

Dan,

Finally getting round to replying to this!

Separation leads to 1 to many relationships. It is theoretically possible for multiple subtypes to share the same supertype. In reality, the mapping is almost always 1 to 1 as in Gus's original problem but it does not rule out the possibility.

Johnny,

Sounds like a logical separation was not necessary but returns to my original point. Controlled denormalisation can do wonders for app speed.

Craig