So, we’re receiving a data feed that looks a bit like this…
| CHILD_TYPE | CHILD_ID | PARENT_ID | PARENT_TYPE | LINKTYPE | START_DATE | END_DATE |
|---|---|---|---|---|---|---|
We have multiple entity types, which in turn have multiple link type codes, which suggests that these are a consolidated list of link tables.
Is it worth splitting them out into several link tables where the link tables are clearly labelled/named, or leaving them inside a similar structure to the above to avoid adding more tables to the design?
Hi Mark,
I think you’ve got the right question from the wrong view point, does the business treat these like different relationships? Are they different flavours of the same overall relationship? Are the number of relationships subject to change? We shouldn’t avoid using the source data to describe how the vault is structured but rather consider it from a top-down approach.
To answer your question more helpfully, by introducing a number of link tables equal to the number of distinct column values you open yourself up to data failures in the future in the case that a new distinct value comes in and is unaccounted for. I’d probably look at putting a link satellite on your link to help describe which link type code your relationship is referring to.
But just to be clear, listen to your business first and forums second when it comes to modelling a DV.
All the best,
Frankie