Mapping/translation table design for one to one or multiple to one condition for mapping

Not strictly an answer... but... my 2cents (not interest bearing, might interest ing).

So, Security X has identifier (ISIN or CUSIP). This identifier doesn't identify the trade, exchange, currency - just the security.

This Security has value, established by trades - which fluctuates over time, place... This is tracked by deals. A deal requires a Security.

Price is parcel of deal.

I would create a table of Securities - Security (it has an ID int identity(1,1) PK), Code Unique constraint, CodeType (CUSIN/ISIN), linksToID (here is a funny: ISIN can have CUSIP too so link both to each other so you can identify the securities by either code) etc.

I would create a table of Deals - Deal (it has an ID int identity(1,1) PK), time, place, and Security.ID, and TYPE).

I would create a table of Prices - Trade (it has an ID int identity (1,1) PK, DealID, Value, currency, TIME, etc)

So, I would create a unique/distinct list of securities, and insert that into Security. Matching the Security to TradeReport (assuming the trade report to be the data you want to capture contains either ISIN or CUSIP but not neither) insert the ID of the matching ISIN of the trade data, and then inserting the matching ID of the CUSIP matching if a linked item isn't already there! to get a unique deal list.

Then I would reference the different deals back to the same import data and capture the prices of the deals to the prices table.

See the hierarchy?

from there you can splice and dice your analysis...

Yeah, that about covers my thinking... hope it helped.

Some constraints are best enforced in the database, and some are best enforced in the application.

Constraints that are best enforced in the database are usually there because they are fundamental to the structure of the data model, such as a foreign key contraint to ensure that a product has a valid category_id.

Constraints that are enforced in an application might not be fundamental to the data model, such as all FooBar products must be blue - but later someone might decide that FooBars can also be yellow. This is application logic that doesn't really need to be in the database, though you could create a separate colours table and the database can require that the product reference a valid entry from that table. BUT the decision that the only record in colours has the value blue would still come from somewhere outside the database.

Consider what would happen if you had no constraints in the database, and required them to all be enforced in the application. What would happen if you had more than one application that needed to work with the data? What would your data look like if the different applications decide to enforce contraints differently?

Your example shows a situation where it might have been more beneficial to have the constraint in the application rather than in the database, but perhaps there was a fundamental problem with the initial data model being too restrictive and inflexible?