How to solve the A/B key problem?

Question

I have a table my_table with these fields: id_a, id_b. So this table basically can reference either an row from table_a with id_a, or an row from table_b with id_b. If I reference a row from table_a, id_b is NULL. If I reference a row from table_b, id_a is NULL.

Currently I feel this is my only/best option I have, so in my table (which has a lot more other fields, btw) I will live with the fact that always one field is NULL.

If you care what this is for: If id_a is specified, I'm linking to a "data type" record set in my meta database, that specifies a particular data type. like varchar(40), for example. But if id_b is specified, I'm linking to a relationship definition recordset that specifies details about an relationship (wheather it's 1:1, 1:n, linking what, with which constraints, etc.). The fields are called a little bit better, of course ;) ...just try to simplify it to the problem.

Edit: If it matters: MySQL, latest version. But don't want to constrain my design to MySQL specific code, as much as possible.

Are there better solutions?

Answer 1

Yes, there are better solutions.

However, since you didn't describe what you're allowed to change, it's difficult to know which alternatives could be used.

Principally, this "exclusive-or" kind of key reference means that A and B are actually two subclasses of a common superclass. You have several ways to changing the A and B tables to unify them into a single table.

One of which is to simply merge the A and B table into a big table.

Another of which is to have a superclass table with the common features of A and B as well as a subtype flag that says which subtype it is. This still involves a join with the subclass table, but the join has an explicit discriminator, and can be done "lazily" by the application rather than in the SQL.

Answer 2

I see no problem with your solution. However, I think you should add CHECK constraints to make sure that exactly one of the fields is null.

Answer 3

Create a parent "super-type" table for both A and B. Reference that table in my_table.

Answer 4

you know, it's hard to tell if there are any better solutions since you've stripped the question of all vital information. with the tiny amount that's still there i'd say that most better solutions involve getting rid of my_table.

Answer 5

A and B are disjoint subtypes in your model.

This can be implemented like this:

refs    (
        type CHAR(1) NOT NULL, ref INT NOT NULL,
        PRIMARY KEY (type, ref),
        CHECK (type IN ('A', 'B'))
        )

table_a (
        type CHAR(1) NOT NULL, id INT NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY (type, id) REFERENCES refs (type, id),
        CHECK (type = 'A'),
        …)

table_b (
        type CHAR(1) NOT NULL, id INT NOT NULL,
        PRIMARY KEY (id),
        FOREIGN KEY (type, id) REFERENCES refs (type, id) ON DELETE CASCADE,
        CHECK (type = 'B'),
        …)

mytable (
        type CHAR(1) NOT NULL, ref INT NOT NULL,
        FOREIGN KEY (type, ref) REFERENCES refs (type, id) ON DELETE CASCADE,
        CHECK (type IN ('A', 'B')),
        …)

Table refs constains all instances of both A and B. It serves no other purpose except policing referential integrity, it won't even participate in the joins.

Note that MySQL accepts CHECK constraints but does not enforce them. You will need to watch your types.

You also should not delete the records from table_a and table_b directly: instead, delete the records from refs which will trigger ON DELETE CASCADE.