Database design SQL Server

Say I hav开发者_运维技巧e a database with multiple entitles like person, company, conference for which you have to keep track of say addresses. We can have multiple addresses for the same entity (person). One approach is to have a separate address table for each entity (person_address etc). Another approach is to have an address table which has primary key (Entity,id,address_type). In this approach we cannot use foreign keys from address table to entities .

So what is the better approach. Is there another way to do this ?

thanks

At a logical modeling POV your descriptions highlights the fact that the entities like person, company, conference etc have a common trait: they have zero, one or more addresses. If you would model this as a class hierarchy, perhaps you would create an Addressable class and have person, company and conference inherit from this Addressable class. You can apply the same reasoning to your data model and have an addresable table with an addressable_entity_id. The person, company, conference entities would 'inherit' this table. There are three established ways to implement table inheritance:

• Class Table Inheritance
• Single Table Inheritance
• Concrete Table Inheritance

So you could model your tables like this:

create table Addresses (AddressId int not null identity(1,1) primary key, ...);
create table Addressable (AddressableId int not null identity (1,1) primary key, ...);
create table AddressableAddress (
    AddressId int not null,
    AddressableId int not null,
    constraint AddressableAddressAddressId
        foreign key (AddressId) references Addresses(AddressId),
    constraint AddressableAddressAddressableId
        foreign key (AddressableId) references Addressable(AddressableId));
create table Person (PersonId int not null identity(1,1) primary key,
     AddressableId int not null, 
     ...,
     constraint PersonAddressableAddressableId
         foreign key AddressableId references Addressable (AddressableId));
create table Company (CompanyId int not null identity(1,1) primary key,
     AddressableId int not null, 
     ...,
     constraint CompanyAddressableAddressableId
         foreign key AddressableId references Addressable (AddressableId));

Of course you have to find the right balance between absolute relational normal form and actual usability. In this scheme I propose for instance in order to insert a new Person one has to first a row in Addressable, get the AddressableId and then proceed and insert the person. This may or may nor work. BTW, there is a way to do such an insert in one single statement using the OUTPUT clause to chain two inserts:

insert into Addressable (AddressableType)
 output  inserted.AddressableId, @FirstName, @LastName 
 into Person (AddressableId, FirstName, LastName) 
values (AddressableTypePerson);

But now is difficult to retrieve the newly inserted PersonId.

Technically if two people live at the same address you would not be completely normalized if there was simply a single one-to-many detail table for the row in TBLPerson called TBLAddress However, if you want just one instance per physical address you will incur the overhead of a many-to-many relation table of TBLPersonAddresses which FK's to TBLAddress

I would say that unless you expect multiple people at the same address to be the norm that I would simply have the TBLAddress with column personID as a detail to the TBLPerson

EDIT: And I tend to always use surrogate keys unless I have a specific reason not to do so.