In our data model, which is derived from the Teradata industry models, we observe a common pattern, where the superclass and subclass relationships in the logical data model are transformed into one-to-one relationships between the parent and the child table. I know you can roll-up or roll-down the attributes to end up with a single table but we are not using this option overall. At the end what we have is a model like this:
Where City Id references a Geographical Area Id.
I am struggling with a good strategy to load the records in these tables.
Option 1: I could select the max(Geographical Area Id) and calculate the next Ids for a batch insert and reuse them for the City Table.
Option 2: I could use an Identity column in the Geographical Area Table and retrieve it after I insert every record in order to use it for the City table.
Any other options?
I need to assess the solution in terms of performance, reliability and maintenance.
Any comment will be appreciated.
Having 1:1 tables in your model means the model was not denormilized effectively.
Where geographical_area may relate to multiple cities in many countries, in your case it may not.'
This should have been denormilized during design.
The example you give does not even carry City_ID or Teritory_ID in the geographical_area table. So what is the relationship ?
Instead of trying to solve the question of how to load these, should you not put in question the validity of these tables.
Folding them into one table, with 2 views on top to satisfy the (invalid) implemented LDM may be a better way to go.
Thanks for your contribution.
Actually I have experience in different DWH approaches (Kimball, Inmon, Data Vault) and now I am using the Teradata Industry Models as a Blueprint for our customers.
In order to resolve the Supertypes and Subtypes relationships in the LDM; Teradata presents 3 Options:
- As-Is: this is the one with the one-to-one relationships, which is the less performant of the the options but it is also the most flexible and scalable. Good performance, easy to understand but not scalable.
- Roll-up: eliminates all the subtypes and include all of their attribute in the supertype. It has a good performance but is less understandable and hard to scale.
- Roll-down: eliminates the supertype and transferrs its attributes to the subtypes
All three options are valid and it should be decided from case to case which one should be selected.
I just want to have a good ETL pattern/strategy for the the tables with one-to-one relationships. The example I mentioned is just to put a non-generic name to the tables (like TableA and TableB).
We are going to implement a highly normalize DWH and if we need denormalization for fast access or performance we can created views or another set of tables in the access layer.
Hier someone gave me a good answer: Insert strategy for tables with one-to-one relationships in Teradata