How do I implement this multi-table database design/constraint, normalized?

Question

I have data that kinda looks like this...

Elements
Class | Synthetic ID (pk)
A     | 2
A     | 3
B     | 4
B     | 5
C     | 6
C     | 7

Elements_Xref
ID (pk) | Synthetic ID | Real ID (fk)
.       | 2            | 77-8F         <--- A class
.       | 3            | 30-7D         <--- A class
.       | 6            | 21-2A         <--- C class
.       | 7            | 30-7D         <--- C class

So I have these elements that are assigned synthetic IDs and are grouped into classes. But these synthetic IDs are then paired with Real IDs that we actually care about. There is also a constraint that a Real ID cannot recur in a single class. How can I capture all of this in one coherent design?

I don't want to jam the Real ID into the upper table because

1. It is nullable (there are periods where we don't know what the Real ID of something should be).
2. It's a foreign key to more data.

Obviously this could be done with triggers acting as constraints, but I'm wondering if this could be implemented with regular constraints/unique indexes. Using SQL Server 2005.

I've thought about having two main tables SyntheticByClass and RealByClass and then putting IDs of those tables into another xref/link table, but that still doesn't guarantee that the classes of both elements match. Also solvable via trigger.

Edit: This is keyword stuffing but I think it has to do with normalization.

Edit^2: As indicated in the comments below, I seem to have implied that foreign keys cannot be nullable. Which is false, they can! But what cannot be done is setting a unique index on fields where NULLs repeat. Although unique indexes support NULL values, they cannot constraint more than one NULL in a set. Since the Real ID assignment is initially sparse, multiple NULL Real IDs per class is more than likely.

Edit^3: Dropped the redundant Elements.ID column.

Edit^4: General observations. There seems to be three major approaches at work, one of which I already mentioned.

1. Triggers. Use a trigger as a constraint to break any data operations that would corrupt the integrity of the data.
2. Index a view that joins the tables. Fantastic, I had no idea you could do that with views and indexes.
3. Create a multi-column foreign key. Didn't think of doing this, didn't know it was possible. Add the Class field to the Xref table. Create a UNIQUE constraint on (Class + Real ID) and a foreign key constraint on (Class + Synthetic ID) back to the Elements table.

Answer 1

I think a trigger is your best option. Constraints can't cross to other tables to get information. Same thing with a unique index (although I suppose a materialized view with an index might be possible), they are unique within the table. When you put the trigger together, remember to do it in a set-based fashion not row-by-row and test with a multi-row insert and multi-row update where the real key is repeated in the dataset.

Answer 2

I don't think either of your two reasons are an obstacle to putting Real ID in Elements. If a given element has 0 or 1 Real IDs (but never more than 1), it should absolutely be in the Elements table. This would then allow you to constrain uniqueness within Class (I think).

Could you expand on your two reasons not to do this?

Answer 3

Comments from before the question was made into a 'bonus' question

What you'd like to be able to do is express that the join of Elements and Elements_Xref has a unique constraint on Class and Real ID. If you had a DBMS that supported SQL-92 ASSERTION constraints, you could do it.

AFAIK, no DBMS supports them, so you are stuck with using triggers.

It seems odd that the design does not constrain Real ID to be unique across classes; from the discussion, it seems that a given Real ID could be part of several different classes. Were the Real ID 'unique unless null', then you would be able to enforce the uniqueness more easily, if the DBMS supported the 'unique unless null' concept (most don't; I believe there is one that does, but I forget which it is).

---

Comments before edits made 2010-02-08

The question rules out 'jamming' the Real_ID in the upper table (Elements); it doesn't rule out including the Class in the lower table (Elements_Xref), which then allows you to create a unique index on Class and Real_ID in Elements_Xref, achieving (I believe) the required result.

It isn't clear from the sample data whether the synthetic ID in the Elements table is unique or whether it can repeat with different classes (or, indeed whether a synthetic ID can be repeated in a single class). Given that there seems to be an ID column (which presumably is unique) as well as the Synthetic ID column, it seems reasonable to suppose that sometimes the synthetic ID repeats - otherwise there are two unique columns in the table for no very good reason. For the most part, it doesn't matter - but it does affect the uniqueness constraint if the class is copied to the Elements_Xref table. One more possibility; maybe the Class is not needed in the Elements table at all; it should live only in the Elements_Xref table. We don't have enough information to tell whether this is a possibility.

---

Comments for changes made 2010-02-08

Now that the Elements table has the Synthetic ID as the primary key, things are somewhat easier. There's a comment that the 'Class' information actually is a 'month', but I'll try to ignore that.

In the Elements_Xref table, we have an unique ID column, and then a Synthetic ID (which is not marked as a foreign key to Elements, but presumably must actually be one), and the Real ID. We can see from the sample data that more than one Synthetic ID can map to a given Real ID. It is not clear why the Elements_Xref table has both the ID column and the Synthetic ID column.

We do not know whether a single Synthetic ID can only map to a single Real ID or whether it can map to several Real ID values.

Since the Synthetic ID is the primary key of Elements, we know that a single Synthetic ID corresponds to a single Class.

We don't know whether the mapping of Synthetic ID to Real ID varies over time (it might as Class is date-related), and whether the old state has to be remembered.

We can assume that the tables are reduced to the bare minimum and that there are other columns in each table, the contents of which are not directly material to the question.

The problem states that the Real ID is a foreign key to other data and can be NULL.

I can't see a perfectly non-redundant design that works.

I think that the Elements_Xref table should contain:

• Synthetic ID
• Class
• Real ID

with (Synthetic ID, Class) as a 'foreign key' referencing Elements, and a NOT NULL constraint on Real ID, and a unique constraint on (Class, Real ID).

The Elements_Xref table only contains rows for which the Real ID is known - and correctly enforces the uniqueness constraint that is needed.

The weird bit is that the (Synthetic ID, Class) data in Elements_Xref must match the same columns in Elements, even though the Synthetic ID is the primary key of Elements.

In IBM Informix Dynamic Server, you can achieve this:

CREATE TABLE elements
(
    class CHAR(1) NOT NULL,
    synthetic_id SERIAL NOT NULL PRIMARY KEY,
    UNIQUE(class, synthetic_id)
);

CREATE TABLE elements_xref
(
    class CHAR(1) NOT NULL,
    synthetic_id INTEGER NOT NULL REFERENCES elements(synthetic_id),
    FOREIGN KEY (class, synthetic_id) REFERENCES elements(class, synthetic_id),
    real_id    CHAR(5) NOT NULL,
    PRIMARY KEY (class, real_id)
);

Answer 4

Create a new table real_elements with fields Real ID, Class and Synthetic ID with a primary key of Class, RealId and add elements when you actually add a RealID

This constrains Real IDs to be unique for a class and gives you a way to match a class and real ID to the synthetic ID

As for Real ID being a foreign key do you mean that if it is in two classes then the data keyed off it will be the same. If so the add another table with key Real Id. This key is then a foreign key into real_elements and any other table needing real ID as foreign key

Answer 5

You can

1. Create a view from the the result set of joining Elements_Xref and Elements together on Synthetic ID

2. add a unique constraint on class, and [Real ID]. In other news, this is also how you do functional indexes in MSSQL, by indexing views.

Here is some sql:

CREATE VIEW unique_const_view AS
SELECT e.[Synthetic ID], e.Class, x.[Real ID]
FROM Elements AS e
JOIN [Elements_Xref] AS x
  ON e.[Synthetic ID] = x.[Synthetic ID]

CREATE UNIQUE INDEX unique_const_view_index ON unique_const_view ( Class, [Real ID] );

Now, apparently, unbeknownst to myself this solution doesn't work in Microsoft-land-place because with MS SQL Server duplicate nulls will violate a UNIQUE constraint: this is against the SQL spec. This is where the problem is discussed about.

This is the Microsoft workaround:

create unique nonclustered index idx on dbo.DimCustomer(emailAddress)
where EmailAddress is not null;

Not sure if that is 2005, or just 2008.

Answer 6

I would:

1. Create a UNIQUE constraint on Elements(Synthetic ID, Class)
2. Add Class column to Elements_Xref
3. Add a FOREIGN KEY constraint on Elements_Xref table, referring to (Synthetic ID, Class)

At this point we know for sure that Elements_Xref.Class always matches Elements.Class.

Now we need to implement "unique when not null" logic. Follow the link and scroll to section "Use Computed Columns to Implement Complex Business Rules": Indexes on Computed Columns: Speed Up Queries, Add Business Rules

Alternatively, you can create an indexed view on (Class, RealID) with WHERE RealID IS NOT NULL in its WHERE clause - that will also enforce "unique when not null" logic.

Answer 7

Create an indexed view for Elements_Xref with Where Real_Id Is Not Null and then create a unique index on that view

Create View Elements_Xref_View With SchemaBinding As
Select Elements.Class, Elements_Xref.Real_Id
From Elements_Xref
Inner Join Element On Elements.Synthetic_Id = Elements_Xref.Synthetic_Id
Where Real_Id Is Not Null
Go

Create Unique Clustered Index Elements_Xref_Unique_Index
On Elements_Xref_View (Class, Real_Id)
Go

This serves no other purpose other than simulating a unique index that treats nulls properly i.e. null != null