How to model single item vs multiple item components
3 posters
Page 1 of 1
How to model single item vs multiple item components
Hi
We are dealing with an existing OLTP model (an ERP application) to create a dimensional model. there are entities like (a) Item (b) item specification (c) Item component. Here below relationships prevail with following cardinality.
1:M between (a), (b)
1:M between (a),(c)
1:M between (b),(c)
Please let me know the best approach to model this. Should we go for any bridge table or Item entity would be connected to the fact table and leave the entities (b) and (c) as they exist today (i.e connected to (a) ).
Thanks
We are dealing with an existing OLTP model (an ERP application) to create a dimensional model. there are entities like (a) Item (b) item specification (c) Item component. Here below relationships prevail with following cardinality.
1:M between (a), (b)
1:M between (a),(c)
1:M between (b),(c)
Please let me know the best approach to model this. Should we go for any bridge table or Item entity would be connected to the fact table and leave the entities (b) and (c) as they exist today (i.e connected to (a) ).
Thanks
dmmodel- Posts : 1
Join date : 2013-12-30
Re: How to model single item vs multiple item components
It depends.
Can an item be a component of another item?
If that is not true, then you can represent the relationship with a simple bridge table containing the item and component keys.
If it is true, you have a recursive hierarchy. You should restructure your model. Items and components would be in the same dimension, with a self-referencing FK for the parent item. Queries would not use the FK directly, instead, you need to create an exploded hierarchy bridge from the recursive relationship to alllow easy use of the hierarchy.
Can an item be a component of another item?
If that is not true, then you can represent the relationship with a simple bridge table containing the item and component keys.
If it is true, you have a recursive hierarchy. You should restructure your model. Items and components would be in the same dimension, with a self-referencing FK for the parent item. Queries would not use the FK directly, instead, you need to create an exploded hierarchy bridge from the recursive relationship to alllow easy use of the hierarchy.
Re: How to model single item vs multiple item components
Set the grain of your fact table at the component level. That would be the easiest way to do it.
This assumes that b's fk to a must be the same as c's fk to a. In other words, a to c is redundant.
This assumes that b's fk to a must be the same as c's fk to a. In other words, a to c is redundant.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» What to do when a single attribute rolls up to multiple categories?
» multiple hierarchy : single dimension vs multiple
» Multiple Date Values for a Single Fact Row
» Multiple Bridge tables to a single fact?
» What to do when a single attribute rolls up to multiple categories?
» multiple hierarchy : single dimension vs multiple
» Multiple Date Values for a Single Fact Row
» Multiple Bridge tables to a single fact?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum