Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Design with a One To One Relationship


By:   |   Updated: 2011-05-16   |   Comments   |   Related: 1 | 2 | More > Database Design

Problem

In order to manage any business situation which may arise, a SQL developer needs to know the basic relationship types and their uses. Perhaps the most rare, a one-to-one arrangement offers a few elegant ways to handle some common business needs. In reading this tip, you will become familiar with some practical implementations of a one-to-one relationship. This article assumes that the reader knows how to create relationships, either through T-SQL commands or data modeling tool.

Solution

Any time two tables have the same primary key and are joined by a foreign key, a one-to-one relationship exists.

The first two examples below use this diagram:

one to one  relationships with t-sql commands or data modeling tool

Scenario 1) A subset of fields. Consider that a system contains people, and not all them are users. All users are people, and could duplicate the fields of [person]. Rather than repeat these data in table [user], creating [user] as a subset of [person] saves disk space by utilizing inheritance. By not having the fields of [user] in [person], all fields in [person] have meaning to its table: normalization. Any updates to personal information can be done with the same interface for [person] and [user] without any duplication of personal data.

Scenario 2) Enforcing permissions based on data within the system. Separate from logins and database users, systems often have users stored in the database as data records. Organized as above, the table [user] ensures that any records therein have a certain level of ability with the system, and associating permissions or roles can be isolated to only users.

Scenario 3) Organizing tables' fields. SQL Server does not have an easy way to reorder a table's columns, and if a table that can have new fields from multiple logical business reasons, they are likely to introduce new columns at different speeds. It is possible to create a one-to-one relationship to isolate which department requested which columns or simply to organize them for better readability of metadata. The potential cost is an extra join in the queries which use the table, but a view which sews them together solves that. An example implementation could be as below, a way to separate business additions from the technical mechanics.

organizing tables fields in sql server
Next Steps
  • Review your schemas to see if any of the above reconfigurations makes sense at the present time.
  • Keep these ideas in mind when designing new tables to see if the concepts can help organize columns or simplify query logic.
  • Come up with other examples to utilize either a subset or inheritance.
  • Download the SQL script used in the three examples above.
  • Check out all of the database design tips.


Last Updated: 2011-05-16


get scripts

next tip button



About the author
MSSQLTips author Brent Shaub Brent Shaub has been creating, renovating and administering databases since 1998.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools