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 a domains table and each domain belongs to a contact. Additionally you can assign 3 extra contacts with different roles admin, tech and billing ,those are not mandatory.

My first thought was to add three extra fields in my domains table.

first thought

but then I thought it was better to create an intersection table.

second thought

Any suggestions?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Using an intersection table is a cleaner and more flexible design.

With extra columns you are limited to the number of instances you can include (one per foreign key column). Also, the types of instances are limited to those given by your FK columns. Making any changes requires a table design change. Also, since your roles are optional, you may have a lot of nulls in your FKs. Having sparsely populated columns rubs some people the wrong way. Whether this is a real design problem or just a "code smell" is debatable and depends on how you actually populate your table.

With an intersection table, you can either use constraints to enforce a single instance per type or you can allow multiple instances per type. That might prove useful in the future if you start having contacts share a role.

Also, with an intersection table, you can create a new role without having to make a table design change. This could save you a lot of work in the future.

share|improve this answer

In both approach you see the table will have all columns to gather where for one column you will always have some value for every row and for remaining you don't know how frequently the column will have value, as they are not mandatory.

In such cases I would separate the data by its probability of availability.

Table_1 = Domain_Owner --> DomainID, OwnerID

Table_2 = Domain_ExtraUser -->DominID,AdminID,TechID,BillingID

Table_3 = Contact --> ContactID

Now in the above design Table2 has same issue. As @JoelBrown mentioned when NULL ability is non-deterministic for column the performance of the queries against them will be unexpected until you do some testing.

If tables are not expected be huge like in millions of rows you can transform Table_2 in to key-value pair.

TABLE_2 = Domain_ExtraUser --> DomainID ,ContactID

share|improve this answer
    
@AnupShap In your last suggestion I can't figure out how I'm going to separate the contacts between their roles? I also want to point one thing, if I wasn't clear enough, that a domain can have partial extra roles, meaning it can have Admin but none Tech or Billing and vice verca. –  giannis christofakis Oct 17 '13 at 6:32

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.