When to use a One-to-One relationships?

[Seth Schrock]

I'm pretty sure that what I have needs to just be combined into one table, but I would still like to know when a one-to-one relationship should be used. The current system uses three Excel sheets. I just made each one into its own table and then created one-to-many relationships. I just found out that one of the tables will only have one record for each record in the main table, so I immediately thought one-to-one relationship. Then I thought why not just combine them? Combining the tables would only add two fields to the main table.

I have looked on Google to find out when one-to-one relationships should be used, but I can't find anything.

[NeoPa]

Only for convenience really Seth. There are no pressing design reasons for one-to-ones. Maybe a situation could arise where the main table consists of only a few fields, but every once in a blue moon a whole swathe of other fields may be required. Access will quite happily handle all these as Nulls if required, but when reviewing the table it might make things a little more clumsy for the designer. As far as performance goes though, I can't think of a scenario where it might be important.

[Seth Schrock]

That is all I figured it would be for, but I know there are many things that I figured would be a certain way that weren't. At least I now know the answer. Thanks NeoPa.

[NeoPa]

No worries Seth. Pleased to set your mind at rest :-)

[ADezii]

A One-to-One Relationship can actually be very handy when you wish to seperate generic/biographical Data from something which is much more sensitive. I am referring to something similar to an Employees Table consisting of Last Name, First Name. Address, City, State, Zip, etc. and a Related Payroll Table consisting the Employee's Salary, Hourly Rate, Bonus Information, Social Security Number, etc. One Table whould be for all to see, whereas the other, consisting of personal/sensitive Data would be only viewable for a select few.

[Seth Schrock]

Interesting idea. I hadn't thought of that type of situation. Thanks for providing that consideration.

[TheSmileyCoder]

I suppose you could have situations where you have a 1-1 relationship between data in your own application and data in another application. You might only have read access to the "other" application but want to record and track other information in your own application.

Business rules can also result in designs where a 1-1 relationship might make sense.