Modelling Part dimensions

View previous topic View next topic Go down

Modelling Part dimensions

Post  kjfischer on Wed Oct 12, 2011 12:23 pm

I work for a company in the distribution business.

We have a part_dimension table:
part_dim_id <-- surrogate key
part_id <-- natural key
vendor_id <-- supplier of the part
manufacturer_part_number <-- actual manufacturer of part

This shows that I can get the same manufactured_part_number from different vendors and assign it a unique identifier based on packaging, etc.

part_dim_id part_id vendor_id manufacturer_part_number
100001 304071 900 08-50-0113
100002 440132 900 08-50-0113
100003 467989 901 08-50-0113

We have a part_price_fact table which contains pricing information for part for different quantity breaks:
part_dim_id
effective_date
currency_code
part_id
quantity
price

The sale price to customers ordering the part is based on quantity and currency:
part_dim_id effective_date currency_code part_id quantity price
100001 10-10-2011 US 304071 1000 20
100001 10-20-2011 US 304071 2000 38
100001 10-20-2011 US 304071 5000 90

We would like to create a fact table for pricing for same products from other sources based on the manufactured_part_number. We are able to collect pricing information for a manufactured_part_number and vendor, i.e.

manufacturer_part_number vendor_id quantity price
08-50-0113 900 1 .03
08-50-0113 900 10 .2
08-50-0113 900 100 2
08-50-0113 900 500 10
08-50-0113 900 1000 20
08-50-0113 900 2000 40
08-50-0113 900 5000 50

The goal is to compare internal pricing to external pricing for the same manufactured_part_number and vendor. But, there is a 1-many between manufactured_part_number and the internal part_id.

Any ideas would be appreciated. Thanks, Kim

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Modelling Part dimensions

Post  ngalemmo on Wed Oct 12, 2011 1:11 pm

First, you fact tables should only contain surrogate foreign keys and measures. So, in your example:

part_dim_id
effective_date
currency_code
part_id
quantity
price

Why do you have part_id? Is it not an attribute of the part dim?

As for the fact you are trying to build, why would you not include part dim id as a FK of the fact? You don't need manufacturer part number in the fact.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Modelling Part dimensions

Post  kjfischer on Wed Oct 12, 2011 2:27 pm

You are correct, there is no reason to carry part_id in part_price_fact.

Regarding, the question of using part_dim_id in the new fact table, we'll call part_external_price_fact. There is a 1-many between a manufacture_part_number, vendor and a part_dim_id (or part_id being the natural key). For the same manufacture_part_number and vendor_id there are more than one part_id.

We are getting prices for the same manufacture_part_number and vendor from various websites also selling the same products. We are trying to compare our pricing with competitor pricing. So, the new fact table will also need to have a website_id as a FK to a website_dimension.

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Modelling Part dimensions

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