CODEWITHSUNDEEP
database-design
James
James

Reputation: 3184

How to reference a table mapping that is not initially known in another table

I have four tables: ExternalName, InternalName, ExternalInternalNameMap and Transaction.
ExternalName and InternalName have a many to many relationship. ExternalInternalNameMap is the junction table between ExternalName and InternalName.

Constraints are:

Thus an ExternalInternalNameMap can have many transactions and a transaction can only correspond to one ExternalInternalNameMap.

enter image description here

The Transaction table must join to the other tables so that the ExternalName and InternalName can be associated with the Transaction.

Having the ExternalInternalNameMap.Id as foreign key in the Transaction table seems a natural choice, but when a row is initially added to the Transaction table, only the ExternalName is known. Later, a user maps an ExternalName to a InternalName. Only then do we have a row in ExternalInternalNameMap. The user needs to see the transaction data to know how to map an ExternalName to InternalName.

Option A:
Transaction could have a fk to ExternalInternalNameMap but InternalNameId (in that table) will initially be NULL. In that case, we cannot enforce duplicate InternalNameId and ExternalNameId combinations in that table.

Option B:
Transaction could contain a fk to ExternalName and fk to InternalName (which would initially be NULL). In that case though, the database does not enforce Transaction to contain valid ExternalName and InternalName mappings.

Is there another approach that resolves these issues?

Edit: Corrected diagram

Upvotes: 3

Views: 162

Answers (3)

Ruud Helderman
Ruud Helderman

Reputation: 11018

One possible way is to introduce the notion of an 'unknown' internal name. For each external name, you also introduce a record in ExternalInternalNameMap that maps that external name to a special internal name 'Unknown'. So for every external name, (at most) one such 'unknown' mapping exists.

Basically, just follow approach A. When you create a transaction, refer to the 'unknown' mapping of the appropriate external name. Once the internal name becomes known, update the transaction; make it refer to the record in ExternalInternalNameMap that contains the 'full' mapping.

Notice we are not introducing duplicate mappings here; you can put a unique constraint on the combination (ExternalNameId, InternalNameId).

Possible variations:

  • Instead of having a dedicated record 'Unknown' (or 'TBD' or whatever) in table InternalName, you may also decide to use a NULL reference in table ExternalInternalNameMap. This does require column InternalNameId to be nullable.
  • You can create the 'unknown' mappings up front (for every record in table ExternalName, there is exactly one record in ExternalInternalNameMap mapping that external name to 'unknown'), or you can create the 'unknown' mapping when you need it (unless it already exists), i.e. when creating the transaction.

Upvotes: 1

Mohsen Heydari
Mohsen Heydari

Reputation: 7284

I will suggest having a combination of A and B options.

enter image description here

To enforce integrity constraint in database design level, you will need to use a composite primary key on the junction table.
PK of ExternalInternalNameMap will be {EN_ID, IN_ID}
Having an optional foreign key of ExternalInternalNameMap inside Transaction table pulse a check constraint will result consistency.

Transaction table = {FK_EN_ID, FK_EINM_EN_ID, FK_EINM_IN_ID, ...}

check constraint on Transaction table = 

{FK_EINM_EN_ID is null OR (FK_EINM_EN_ID = FK_EN_ID)}

Upvotes: 1

Rick James
Rick James

Reputation: 142366

FOREIGN KEYS cannot solve everything; forget about them for the moment...

When a new Transaction comes along:

  1. Insert into Internal and/or External if those names do not already exist
  2. Insert into Map if the relation does not already exist
  3. INSERT into Transactions.

Note that by doing these three steps in that order you always are guaranteed that a row exists before you establish a FK relation to it. (Oops -- why bother declaring FKs?)

So, I guess I am voting for something like Option A.

Keep in mind that whenever you need to get to Internal from Transaction, you will need to go through Map -- that is two JOINs. And, similarly, 2 JOINs to go the other direction. So, be sure there are INDEXes for each direction. (A FK generates an index in one direction.)

Upvotes: 0

Related Questions