Thoughts on one-to-one relationships

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Thoughts on one-to-one relationships

Author

Topic

evesnight
Starting Member

10 Posts

Posted - 2010-07-26 : 21:07:40

I haven't used one-to-one's too much in the past and I just wanted to get everyone's thoughts on their use.

So I found myself staring at a bunch of tables that had a bunch of the same fields in them. In an object oriented language, this makes sense as one class inherits from a parent class. However in DB the only way to accomplish this seems to be a one-to-one. So instead of having 4 or 5 tables that have duplicated fields, is it better to implement a more object oriented design pattern in SQL?

So instead of this:
CREATE TABLE Employee (
EmpID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VACHAR(50) NULL,
...
)

CREATE TABLE Customer (
CustID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VACHAR(50) NULL,
...
)

One can do this instead

CREATE TABLE Person (
PerID INT PRIMARY KEY NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VACHAR(50) NULL,
...
)

And then

CREATE TABLE Employee (
PerID INT PRIMARY KEY NOT NULL, --Relates back to Person_PK
... Columns specific to an employee
)

CREATE TABLE Customer (
PerID INT PRIMARY KEY NOT NULL, --Relates back to Person_PK
... Columns specific to a customer
)

So the downsides are obviously you have to manage 2 tables on the back end and I am wondering if there are any huge downsides to doing this. obviously there is an extra table to deal with when updating/inserting new records but it seems like it would be a more elegant way of designing in these circumstances.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-07-27 : 00:23:35

this is a normal step of database normalization (read more on this issue: http://en.wikipedia.org/wiki/Database_normalization) and as long as your database schema is following your application model, this is the only correct way to do it.

you should ask yourself what is the difference between Customer and Employee or Customer and Person and if there are differences in database structure, usage, data model, than this is perfect.

in my system i have tables like: Person, Customer (both tables have completely different structure) but no employee, because everything that employee and person can be is stored in Person table and customer is strictly data on our customers.

So go through your data/bussines model again and rethink how many tables you need and what will be stored inside this tables.

evesnight
Starting Member

10 Posts

Posted - 2010-07-27 : 14:45:48

Yes I have gone through my requirements and a one-to-one model seems to fit this scenario. I guess I am bringing this topic up because I have genreally avoided using one-to-one's in the past for a few reasons mainly with respect to concerns over performance. If you have one shared table instead of multiple tables then that table will get locked more frequently and could cause performance degredation when the app scales. I guess this is something I will have to stress test to see if that concern is trivial compared to the elegance of the design.

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-27 : 15:15:09

Can't a Person be employed more than once for different comapnies?

Can't a person be a customer of many different vendors/companies?

In any case, even if it is 1-1, the elimination of redundant data is a key to normalization

And I would not use the PersonID as the key to the other tables..they are not the same "thing"

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

dportas
Yak Posting Veteran

53 Posts

Posted - 2010-07-28 : 17:09:28

Just keep in mind that the structure you described is not in fact one to one but one-or-zero to one. That's because foreign keys are always optional on one side of the constraint. The parent row must exist but the referencing row doesn't have to. It is exactly this property that makes such structures useful - not every person is a customer.

evesnight
Starting Member

10 Posts

Posted - 2010-07-29 : 14:13:06

Right it would be a 0-to-1 up to a 1-to-1

The customer example I gave was just an example. I actually work more with financial databases.

Celko
Starting Member

23 Posts

Posted - 2010-07-29 : 14:37:32

>> In an object oriented language, this makes sense as one class inherits from a parent class. However in DB the only way to accomplish this seems to be a one-to-one. So instead of having 4 or 5 tables that have duplicated fields [sic: columns are not fields], is it better to implement a more object oriented design pattern in SQL? <<

You OO guys have an "Employee" object that you use one at a time. We RDBMS guys have a "Personnel" set and we work on the entire thing all at once . We have a different mindset and you see it in the table names.

We ask about attributes versus entities. Is "customer-ness" an attribute of a person or are customers a set of the same kind of entity? Are we modeling persons or totally different roles that are played by persons? Since a customer can be a corporation or other "lawful person", this example might have problems.

It all depends ..

Author of
SQL for Smarties
Thinking in Sets
SQL Puzzles and Answers
Analytics and OLAP in SQL
SQL Programming Style
Standards in SQL
Data & Databases
Trees & Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources