In a former post about Quipu i showed you, how you could generate a datavault datamodel from a 3NF datamodel. For that post i used the AdventureWorksLT2008R2 database. Now it's time to dive into the generated datamodel of the tables that are generated by Quipu. The datamodel of the AdventureWorksLT2008R2 is presented below:
This is subset of the complex AdventureWorks2008R2 database and regarding the complexity it is comparable with the Nortwind database of SQL Server 2000.
I analyzed the generated datavault datamodel by Quipu and i conclude that the following links, satellites and hubs have been identified by Quipu:
The generated Datavault model that is generated by Quipu is presented in diagram below:
The blue tables are the links, the orange tables are hubs and the yellow one are the satellites. I organized the diagram in a way that it looks like as much as possible on the diagram on top of this post.
I was quite some work to gather the information but it looks great and it was learnful.
The following conclusions can be drawn from the generated model:
The tables generated seems to be well structured and have a logic naming convention.
Some of the fields are named lid (link id), hid (hub id) and mid (don't know what m meand in this concext. M from 1:m?).
All the tables who have a foreign key in a referenced table are named FK_... (FK means foreign key table?).
When the table has a reference from a foreign key and not referencing an other table by a foreign relationship it doesn't have a FK_ naming convention.
Update 11 november (from a reaction):
- mid is the 'master id', the 1 side of the 1:n relation.
- relations can be grouped in the staging model resulting in 'fatter' but less flexible links.
- the names of links derived from a relationship are named after the FK. So giving the FK's a nice name results in a nice named link ;)
- links based on staging tables are named after the table hence the differense between the links.