I need a DB that can capture instruments as reported by a bank and two accounting firms. Let's take Walmart stock as an example. The bank may have a instrument named "Wal-Mart Stock". One of the accounting firms may have it named as "Walmart Stock". The other accounting firm may have "Wal-mart common stock".
Here's the diagram that I have so far:
* Table contains five additional columns. These five column names are the same for
DataSource represents the bank, accounting firms, or any other entity that can provide data.
DataSourceInstrument represents the instrument data as provided by the
MasterInstrument is a version of the instrument specific to the application itself. So, in our example, it may contain "Walmart Common Stock" for the instrument name. It's different than
DataSourceInstrument b/c unlike
DataSourceInstrument, it is used extensively throughout the application and it must maintain a history of changes.
Instrument table provides a way to know that several different data sources are referring to the same instrument.
MasterInstrument with the same
instrumentId are referring to the same instrument. (
Instrument.id is a surrogate key b/c some instruments do not have a natural key, and the
DataSourceInstruments and a
MasterInstrument may need to be manually mapped as referring to the same instrument.) So, using the Walmart example, we can know that the bank, two accounting firms and the internal system are referring to the same Walmart instrument.
Issuer represents the issuer of the instrument such as "Wal-Mart Stores, Inc." Note: It only needs to be captured for
I have a couple of doubts on this design:
- Is it poor design to have a table (i.e.
Instrument) with only a single surrogate column? If this is a problem, how would it be designed differently?
- Are the various *Instrument table names confusing? If so, how could it be more clear?
Overall how should the design account for the need to capture multiple sources referring to the same instrument along with the need to capture an internal version of that instrument with history?
The system's primary function / raison d'être is reporting. It must report for a given date what the internal instrument (i.e.
MasterInstrument) data was on that date. This is why I have
MasterInstrument as a history /temporal table and do not have the columns in