CODEWITHSUNDEEP
sql-serverdatabase-designdatabase-schema
spocky
spocky

Reputation: 113

Linking an address table to multiple other tables

I have been asked to add a new address book table to our database (SQL Server 2012).

To simplify the related part of the database, there are three tables each linked to each other in a one to many fashion: Company (has many) Products (has many) Projects and the idea is that one or many addresses will be able to exist at any one of these levels. The thinking is that in the front-end system, a user will be able to view and select specific addresses for the project they specify and more generic addresses relating to its parent product and company. The issue now if how best to model this in the database.

I have thought of two possible ideas so far so wonder if anyone has had a similar type of relationship to model themselves and how they implemented it?

Idea one: The new address table will additionally contain three fields: companyID, productID and projectID. These fields will be related to the relevant tables and be nullable to represent company and product level addresses. e.g. companyID 2, productID 1, projectID NULL is a product level address. My issue with this is that I am storing the relationship information in the table so if a project is ever changed to be related to a different product, the data in this table will be incorrect. I could potentially NULL all but the level I am interested in but this will make getting parent addresses a little harder to get

Idea two: On the address table have a typeID and a genericID. genericID could contain the IDs from the Company, Product and Project tables with the typeID determining which table it came from. I am a little stuck how to set up the necessary constraints to do this though and wonder if this is going to get tricky to deal with in the future

Many thanks,

Upvotes: 6

Views: 2633

Answers (2)

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

I will suggest using Idea one and preventing Idea two.

Second Idea is called Polymorphic Association anti pattern
Objective: Reference Multiple Parents
Resulting side effect: Using dual-purpose foreign key will violating first normal form (atomic issue), loosing referential integrity
Solution: Simplify the Relationship

The simplification of the relationship could be obtained in two ways:

  • Having multiple null-able forging keys (idea number 1): That will be simple and applicable if the tables(product, project,...) that using the relation are limited. (think about when they grow up to more)
  • Another more generic solution will be using inheritance. Defining a new entity as the base table for (product, project,...) to satisfy Addressable. May naming it organization-unit be more rational. Primary key of this organization_unit table will be the primary key of (product, project,...). Other collections like Address, Image, Contract ... tables will have a relation to this base table. enter image description here

Upvotes: 2

Aaron
Aaron

Reputation: 161

It sounds like you could use Junction tables http://en.wikipedia.org/wiki/Junction_table. They will give you the flexibility you need to maintain your foreign key restraints, as well as share addresses between levels or entities if that is desired.

One for Company_Address, Product_Address, and Project_Address

Upvotes: 0

Related Questions