CODEWITHSUNDEEP
sqlsql-serverdatabase-design
Dai
Dai

Reputation: 155055

Enforcing referential integrity from one table to any table

I have a variety of tables that represent business objects, e.g. Products, People, Locations and I'm wanting to add a Tags table (as in taxonomy).

CREATE TABLE Tags (
    tagId bigint IDENTITY,
    name nvarchar(50)
)

How can I allow multiple tags to be applied to multiple types of entities in a way that allows the DBMS to enforce referential integrity without needing a linking table like this:

CREATE TABLE TagUse (
    tagId bigint,
    productId bigint NULL,
    personId bigint NULL,
    locationId bigint NULL,
    ...        
    whateverId bigint NULL
)

Or worse:

CREATE TABLE PersonTags (
    tagId bigint,
    personId bigint
)

CREATE TABLE LocationTags (
    tagId bigint,
    locationId bigint
)

...

CREATE TABLE WhateverTags (
    tagId bigint,
    whateverId bigint
)

I just thought of a third option: rather than having separate *Tags tables for each entity, each entity can be thought of inheriting from "Taggable" which is then referenced-to by the child tables:

CREATE TABLE Taggable (
    taggableId bigint,
    tagId bigint
)

CREATE TABLE Persons (
    personId bigint,
    ...
    taggableId bigint
)

Upvotes: 1

Views: 70

Answers (1)

Menno
Menno

Reputation: 12621

You could use a sequence object:

CREATE TABLE [TaggableObjects] (
    objectId BIGINT IDENTITY(1,1)
)

CREATE TABLE [Persons] (
    objectId BIGINT, -- referencing Objects.objectId
    name VARCHAR(50)
)

CREATE TABLE [Locations] (
    objectId BIGINT, -- referencing Objects.objectId
    country VARCHAR(50)
)

In programming languages like Java and C# you'd call Persons and Locations an extension of TaggableObject.

Following this you could implement the Tags as:

CREATE TABLE [Tags] (
    tagId BIGINT IDENTITY(1,1),
    objectId BIGINT
)

Though the problem with an implementation like this one would be that you have to figure out the best way to identify of what type Tags.objectId is.

Note that depending on which engine you'll be using, many support some sort of functionality to more easily implement a sequence object, you might want to right up on it.

Upvotes: 1

Related Questions