SQL Database Design

Question

I have a design dilemma; On one hand (option 1) I only create address table one time but on the other hand (option 2) seems like it would have performance advantages...less joins and less data. What do you guys think?

@eugv86 How do you have a one to many relationship between client/employee/contact and address? In most scenarios, one client/employee/contact is allotted two addresses. — HappyCoding, Feb 18 '16 at 22:32
If the addresses all contain the same information, then one table is a no-brainer. If the addresses have special information for each type, then more thinking is required. — Gordon Linoff, Feb 18 '16 at 22:33
An extension to option 1 (you probably just want answers not more options) is to have only one linking table, not three. One thing to think about is: will three different client, employee, contact have the same address and do you care if they do? That's what model 1 supports. The fact is if they are the same person, then usually at the data entry stage they are not identified as the same person and therefore are not attached to the same address. You might want to get back and think about whether you need three different client/contact/employee tables or if just one will do the trick. — Nick.Mc, Feb 18 '16 at 23:04
I'm just not convinced that option1 is necessary in my case, thanks all — DonkeySticks, Feb 19 '16 at 16:15
It depends on your requirements. Do client, employees, contacts need to be able to share the same address? — Nick.Mc, Feb 20 '16 at 02:35

score 0 · Answer 1 · edited May 23 '17 at 10:30

Option 1 for sure. All your addresses should be in one spot. This shouldn't hinder performance either. You can limit the addresses returned by the WHERE statement when you query the data.

Three customer addresses in one table or in separate tables?
Referencing the aforementioned link: Since you're saying your customer address relationship is modeled as one-to-many, I would use the following example of one address table, an AddressType and a EntityId FK. Using this method would allow for an Entity (client/employee/contact) to have many addresses.

CREATE TABLE Entity
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    Name varchar(60) not null,
    EntityType int not null
    -- etc
)

CREATE TABLE Address
(
    ID int not null IDENTITY(1,1) PRIMARY KEY,
    EntityID int not null
        CONSTRAINT FK_Entity_EntityID FOREIGN KEY References Entity(ID),
    Street varchar(120) not null,
    AddressType int not null 
    -- etc
)

This is insufficient to represent the case where 1 address maps to N Entities. Relationship between Entity and Address is NxM. — Ivo, Feb 18 '16 at 23:03

score 0 · Answer 2 · answered Feb 18 '16 at 23:00

Option 1 is better from a design perspective. One table should have all the addresses - and kept updated accordingly. This way you can easily create relationships between addresses and Client, Employee and Contact (and everything else really) and do queries like: "select everything that lives in X address". Keep in mind that this way you will have to properly maintain your Address table - manage duplicate addresses, make sure the address is correct etc.

On the other hand, in a very simplistic Use-Case where you just want to display a Client, Employee and Contact info, an Address can be a free-text column. This will save you the trouble of properly maintaining addresses.

It all comes down to what you need in the end.

SQL Database Design

2 Answers2