6

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,

spocky
  • 113
  • 6
  • 2
    You have a third option and that is to have the address table as the primary table and have an AddressID in the 3 other tables? Then your relationship problem is sorted out – Jaques Nov 27 '14 at 11:05
  • 2
    Hi Jaques, many thanks for the reply. I'm afraid my question was not very clear. Each of my three tables: Company, Product and Project can each have multiple addresses rather than just the one – spocky Nov 27 '14 at 11:16
  • What does it mean for a company to have an address, for a product to have an address, and for a project to have an address? – philipxy Nov 28 '14 at 09:32
  • 1
    Hi, well the addresses I will be storing are actually invoice addresses - who to pay money to when we have completed some work on a project. In the case of a company, this could be for example a multi-national company with several different regional top-level addresses. So thinking of a company like Sony, they may have two or three top level HQ addresses. Sony offer many products where some are big enough to warrant their own specific offices e.g. Televisions. I may then be told that there is a specific invoice address for a particular project that they offer. – spocky Nov 28 '14 at 11:47

2 Answers2

2

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
Community
  • 1
  • 1
Mohsen Heydari
  • 7,256
  • 4
  • 31
  • 46
  • Many thanks for the comment Mohsen and the reference to the anti pattern name which has been useful for googling for more information. I agree that having this base table should satisfy this requirement whilst keeping referential integrity though what would your opinion be if I wanted to join other tables to addresses in this fashion? Before I know it, most tables could be having their main key in this base table so maybe having primary keys as GUIDs could be an option? – spocky Dec 02 '14 at 14:28
  • Exactly yes. A GUID will be helpful. About joining other tables, if you mean joins like Product to Address, there will be no problem in queries, since PK of Product is the PK of OrhganizationUnit and Address Have a FK of OrhganizationUnit table. – Mohsen Heydari Dec 02 '14 at 15:15
  • @MohsenHeydari, how does this solve if one product has multiple addresses since PK of organizationUnit is PK of product. I believe, two allow multiple addresses, OrganizationUnit has to have composite primary key of PK + FK of Address. Do you agree? – Ketan Apr 06 '20 at 20:59
0

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

Aaron
  • 151
  • 3
  • Thanks for the comment Aaron. I did think about using junction tables though my concern was the possibility of addresses getting orphaned (not related to a company, product or project). I also don't think I need an address to be shared between the different levels as the nature of the address being related to a table in the company > product > project hierarchy already handles this sharing. I will give it some further thought though – spocky Nov 27 '14 at 12:31
  • That makes sense if your worried about orphaned records. One option would be to go with Idea two, and enforce the referential integrity via triggers rather than a constraint or using declarative referential integrity. In old SQL-Server that was the standard way to enforce it, and it is more flexible for having keys from multiple tables in one column. – Aaron Nov 27 '14 at 21:10