0

I have a relationship/linking table which is intended to hold relationships against more than one base table. that is, we have this general idea of Tags which can be applied to several different entities, let's say Company, Building, and Customer. Each of these entities has a unique Id column that's an identity col. I have logically assigned the 3 base entities an enumeration value, so Company is 1, Building 2, and Customer is 3.

What I've done, then, is generically associate tags based on a logical compound key of entity type and entity id.

EntityType   EntityId   TagId
-----------------------------
     1          99       22

I want to enforce that if entity type is 1, representing a company record, that there is a record in the company table with that id (99, in the above example). Of course I can't add a typical foreign key to the company table's Id column because not all of the linking table records refer company records. I need something that checks the correct base table based on the EntityType value.

I've searched but I can't find any examples for this, though I have to believe it's not uncommon. TIA.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tntwyckoff
  • 539
  • 5
  • 17
  • 1
    You would have to use a UDF for this. Or have a separate column for CompanyID, BuildingID and CustomerID. And you could build a constraint that would let your keep your enumeration and validate that only 1 foreign key column is populated – Sean Lange Sep 23 '19 at 15:32
  • It might be worth reading some of the (lower down) answers to this question: https://stackoverflow.com/questions/4452132/add-foreign-key-relationship-between-two-databases. I know this isn't what you are looking for, but some of the solutions are actually quite similar, e.g. using a constraint based on a UDF. – Richard Hansell Sep 23 '19 at 15:48
  • Can you refactor this table into separate tables for CompanyTags, BuildingTags, CustomerTags, etc.? This would enable you to have simple foreign keys setup between each table and the Tags table. – Paul Williams Sep 23 '19 at 16:03
  • The only time that I saw anything like this in actual production was related to data that was both hierarchical and multisourced, and the solution wasn't coded into the database. Accesses for the tables were coded into shared code, and all access code included the verification before adding the data into the tables... because it is far easier in this case. However, there is another, simpler solution that can be used... – Laughing Vergil Sep 23 '19 at 16:55
  • ok, to me having explicit ID columns per type, each with a FK seems to be the closest viable solution to what i want, only slightly less efficient than re-using a generic column. i was trying to be cheeky and avoid DB changes when we apply the existing idea to a new entity type, but it does get messy. – tntwyckoff Sep 23 '19 at 18:28

1 Answers1

0

If you want all of your [Tags] in one table, and there is a likelihood that you will use the same tag against more than one source table, the better solution would be four total tables - [Tags], [CustomerTags], [CompanyTags], and [BuildingTags]. The three tables would contain linking records. For example, `[CustomerTags] might look like:

CREATE TABLE [CustomerTags] (
    TagID int not null,
    CustomerId int not null
)

Indexes on each id with the other id Included into the index. The other two tables would look similarly. This removes a layer of difficult-to-make-work logic, and makes coding it far easier.

Laughing Vergil
  • 3,706
  • 1
  • 14
  • 28