CODEWITHSUNDEEP
sqlsql-serverdatabase-designforeign-keysddl
Brendon Dugan
Brendon Dugan

Reputation: 2168

Database Design, Table emulating foreign keys to multiple tables

I am trying to solve a problem and having a difficult time coming up with the best solution. I have a database which contains (among other things) a table for the following things:

Each of these tables has a column for comments (from the end user to the admin), however I have been asked to add the ability to add notes to all of these requests. I would like each set of notes to be separate so that I can track the Date/Time the note was added, as well as who edited it. This suggests to me that I need to store the notes in a table, and link back to the request through a foreign key. The problem is that each request table has an auto incrementing id column which is unique in that table, but not unique to all of the others (meaning that each table could have a request with an id of 200).

I realize that one solution to my problem could be to create a "Notes" table for each type of request and create the foreign key accordingly, but that surely isn't the only decent way to solve the problem.

What I really would like to know is whether there is a valid way of using something like the id and request type (table name basically) to create a foreign key. Is this possible?

Upvotes: 1

Views: 1716

Answers (2)

nairbv
nairbv

Reputation: 4323

You can have a table Note( object_id, note ) where object_id references any of the request tables. You don't really have to have a foreign key constraint in the database, or if you really want to maintain a constraint, you can use a constraint clause (might be vendor specific?).

To determine which request table a note is associated with, you could have an enum type value in Note. Some database vendors have an Enum type, others you might just use an Int. It would just be some column like request_type enum(DESTINATION, SUPPORT, etc....).

If your database is small or can be taken offline for a while, you could instead just recreate the tables such that they use a single sequence that generates all four of their object_id's... that way ID's would be unique across the different request tables. I like having a universally unique object_id sequence for this kind of situation.

With some vendors (postgres), you can use table inheritance. With table inheritance you would do something like create table destination_request(....) inherits(note); This would make any fields in the note table available to queries on the destination_request table. This does in a sense work, and does the work for you behind the scenes. Conceptually a request is not a subtype of note, so this isn't ideal in terms of OO design, and table inheritance is likely not portable to other database vendors. It's up to you how concerned you are with purity here.

You could have four different foreign keys pointing at each table as suggested above, but that leaves you with a table of mostly Null values... I would probably prefer having four different note tables over having a table with that many null values.

Upvotes: 1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

As a band-aid, you can do something like this...

enter image description here

...with the following constraint on Note:

CHECK (
    (
        “Destination Request Id” IS NOT NULL
        AND “Support Request Id” IS NULL
        AND “Exchange Request Id” IS NULL
        AND “Deposit Request Id” IS NULL
    )
    OR (
        “Destination Request Id” IS NULL
        AND “Support Request Id” IS NOT NULL
        AND “Exchange Request Id” IS NULL
        AND “Deposit Request Id” IS NULL
    )
    OR (
        “Destination Request Id” IS NULL
        AND “Support Request Id” IS NULL
        AND “Exchange Request Id” IS NOT NULL
        AND “Deposit Request Id” IS NULL
    )
    OR (
        “Destination Request Id” IS NULL
        AND “Support Request Id” IS NULL
        AND “Exchange Request Id” IS NULL
        AND “Deposit Request Id” IS NOT NULL
    )
)

This way, you don't have to change PKs of existing tables (which could have cascading effect on the rest of your model and the client application(s)), yet you can have proper referential integrity without "repeating" the Notes table.

Upvotes: 3

Related Questions