How to model single item vs multiple item components

View previous topic View next topic Go down

How to model single item vs multiple item components

Post  dmmodel on 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

View user profile

Back to top Go down

Re: How to model single item vs multiple item components

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: How to model single item vs multiple item components

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: How to model single item vs multiple item components

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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