[SQL] SQL Define Relationship Involving a Common Columns in Multiple Tables

This is my first post so please forgive me if this has been answered elsewhere. There are sort of similar posts out there already such as Define common fields in multiple tables but nothing that really relates to what I need as far as I can tell.

I have 3 tables. Two of the tables, Clients (PK: ClientId) and Staff (PK: StaffId), have very different data and numbers of columns - however they both have a Location (VARCHAR) column that stores a lat/long location. The third table is Distances in which I want to store the distances between any two places where each place could be either a client or staff.

To keep it simple, my goal is to be able to set it up in such a way that you could say construct the required relationships using say a typical relationships editor where you can see the tables visually and the links between them (i.e. create the constraints that enforce referential integrity).

I imagine that the columns in the Distances table will include the columns Place1Id, Place2Id and Dist, where Place1Id and Place2Id are the PKs of either the relevant client or staff table.

Clearly this is not enough because we can't yet tell if Place1Id refers to a school or client. I could add in another column into Distances called PlaceTypeId and have another table PlaceTypes containing a PlaceTypeId (say 1 for clients and 2 for staff) and say a VARCHAR Name (being either School or Client) - but this still wouldn't work in terms of being able to create the necessary relationships. For one, we haven't told the db anywhere that 1 refers to clients and 2 refers to staff.

Is there a way to achieve my goal elegantly (i.e. simple enough to be able to be done with say a visual relationships editor), or must I go down the path of something like storing the PlaceTypeId type in every row in Clients (which would all be 1), and in Staff (which would all be 2 and feels like replicating data unnecessarily), and then creating a unique key with via a combination of PlaceTypeId and ClientId or StaffId? (And then you can't do that with integers I assume because just adding them won't work of course...).

Continue reading...