address table with two foreign keys. is this proper?

[delikedi]

My addresses table used to have a single foreign key - customers. Now that "buildings" entity is introduced, I also need to store addresses of buildings of interest.

If I were at the design stages of the database, I would have created a master "address owners" table, with one-to-one relationships to "customers" and "buildings" and a one to many relationship to addresses. However, the database is in use.

In order to relate "buildings" to "addresses" with referential integrity, I added another FK column to "addresses" to hold "buildings" PK. This works as intended, however now the addresses table have two FK columns that are both destined to be half null forever. My fear is that would this structure cause problems down the road? Is it worth it to spend some hours to merge the PK's of my master tables (customers and buildings) into an "address owners" table whose sole purpose is to generate autonumber PK's for those tables?

 

[ButtonMoon]

A building can have more than one address? If that's the case then I suggest you create a "BuildingAddress" table - the key of BuildingAddress being whatever is the key of the address table (e.g. AddressId).

Similarly for customers if a customer can have more than one address.

 

[delikedi]

you are right, buildings only have one address. then again, I considered the relation being one-to-many a relatively acceptable mishap. customers have more than one address though, home/work/construction site etc.

I considered creating a separate table too. Actually the original buildings table had address fields.

Still, my question stands. Maybe it would be better to refine it as follows:

What is the proper way to for an entity (i.e. addresses) to accept foreign keys from multiple entities (i.e. customers, companies) while preserving full referential integrity?

One can solve this problem by introducing a FK column for each entity. You can have

Addresses
------------
Addres ID (PK)
CustomerID (FK)
CompanyID (FK)
FactoryID (FK)
RecreationalActivityID (FK)
AddressDetail1
AddressDetail1
City
Country

however this will mean that a large portion of any one of the FK columns have to be null forever, which I believe is a violation of normalisation principles.

 

[ButtonMoon]

you are right, buildings only have one address.

Then I expect you ought to put an address foreign key in the buildings table.

Still, my question stands. Maybe it would be better to refine it as follows:

What is the proper way to for an entity (i.e. addresses) to accept foreign keys from multiple entities (i.e. customers, companies) while preserving full referential integrity?

Use a supertype/subtype pattern. E.g. create a "Party" table which is the supertype of customers and companies. You can find quite extensive material in the database design literature on how to represent supertype/subtype patterns and the pros and cons of different approaches. As a rule (see The Principle of Orthogonal Design) attributes like the address which are shared by one or more subtypes should be pushed up the hierarchy so that they only appear in one table.

amazon.com/Information-Modeling-Relational-Databases-Management/dp/0123735688
amazon.com/Date-Database-Writings-2000-2006-C/dp/159059746X
dbdebunk.com/page/page/622331.htm
dbdebunk.com/page/page/622312.htm

Also of interest, the Party Data Model: tdan.com/view-articles/5014/

 

[delikedi]

Thank you for your answer. It is clearer now that I have to merge these entities with a supertype named "address owners" and use its PK as the sole FK in my addresses table. Maybe in the long run, there may arise other address-owning entities such as outdoor organizations etc. which would be much easier to implement in the database. If I were to retain the current structure, I would have to add more FK columns to addresses and use extra code to manage it.