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:
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.
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.
