Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

How to model single item vs multiple item components

3 posters

Go down

How to model single item vs multiple item components Empty How to model single item vs multiple item components

Post  dmmodel Tue Sep 09, 2014 5:34 am

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

dmmodel

Posts : 1
Join date : 2013-12-30

Back to top Go down

How to model single item vs multiple item components Empty Re: How to model single item vs multiple item components

Post  ngalemmo Tue Sep 09, 2014 11:46 am

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.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

How to model single item vs multiple item components Empty Re: How to model single item vs multiple item components

Post  BoxesAndLines Wed Sep 10, 2014 8:24 pm

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.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

How to model single item vs multiple item components Empty Re: How to model single item vs multiple item components

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum