mercredi 25 février 2015

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.


A Transaction can only have exactly one ExternalName and exactly one InternalName associated with it. An ExternalName can have many transactions. An InternalName can have many transactions. 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.


Alternatively (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?





Aucun commentaire:

Enregistrer un commentaire