Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have "1 <-> 0..1 relationship". For example:

There is a set of users. Some users are customers.

Thus we have two tables: the table users and the table customers.

What is the best way to express this in a relational database:

  1. In the table users add the customer field which may be either a ref to customers or NULL.

  2. In the table customers add UNIQUE user field which refers to users table.

I have already asked a similar question in some forums, but the answer was basically "whatever you need", "whatever you think convenient". I don't like this kind of answer. I want a serious piece of DB theory instead, a well founded answer. Where to read about 1 <-> 0..1 relationships?

share|improve this question
1  
I would think add it as a FK to customers since it's an aspect of the customer record, not the other way round. –  JNK Dec 13 '11 at 16:07
    
@JNK: What is "FK"? –  porton Dec 13 '11 at 16:16
    
Foreign Key - it's an enforced relationship to be certain that a userid exists that matches the value listed in the customer table –  JNK Dec 13 '11 at 16:17
    
Correction: "which may be either a ref to users or NULL" -> "which may be either a ref to customers" or NULL –  porton Dec 13 '11 at 20:13
add comment

2 Answers 2

up vote 3 down vote accepted

I want a serious piece of DB theory

Modern relational theory rejects nulls, which would seem to immediately invalidate your option 1. However, this strawman may be eliminated by replacing the default null with a default value e.g. a 'dummy' customer created solely to explicitly model the "is not a customer" correspondence.

I think your option 2 is the most theoretically sound because, unlike the modified option 1, the relations can be in sixth normal form (6NF), being a projection-join normal form and the highest normal form attainable.

I have also heard of a design rule of thumb that states that a relation should model EITHER an entity OR the relationship between entities but never both, which seems sensible to me. Again, this would favour option 2. However, I heard of this rule of thumb many years ago, don't recall where and can offer no serious theoretical basis (other than 6NF as mentioned above).

share|improve this answer
add comment

You have been given the correct answer in part. The real answer comes from your data model and how it got normalized. A key is how you get to the relationship:

  • The customers table consists of a number of fields considered for the users table which belong to the the customer concept, and are null unless the user is also a customer (sub-type of user). In this case the customers table inherits the primary key from the users table. (It is possible to multiple sub-types which may or may not overlap.)

  • The customers table consists of a number of fields related to the customer concept, but not necessarily the user concept. The customer is a strong table and is not dependent on the user concept. (Removing the users table would not significantly impact the design of the customers table.) In this case the customers table gets its own primary key.

What you are have is a special case of optional one to many relationship where the upper bound is 1. Consider it from both sides: Would it be possible for one user to have multiple customers, or one customer to have multiple users. If so you will need to remodel your data.

Adding the user-id foreign key to the customers table could be considered a better choice as correctly maps the one to many (upper limit 1) relationship and avoids a nullable field. To enforce the upperlimit the foreign key index needs to be unique. This will occur automatically if the primary key is the user-id.

Adding the customer-id as an optional foreign key to the customers table enforces the upper limit of 1 in the relationship but reverses the dependency.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.