How to handle situations when there is a combined primary key in the source system?

View previous topic View next topic Go down

How to handle situations when there is a combined primary key in the source system?

Post  thomaszhwang on Wed Aug 17, 2011 4:32 pm

Now I have an Invoice dimension table. In its original source system, it uses the Project_ID and the Task_ID as a combined primary key. In this case, in my dimensional modeling, of course, I will have an Invoice_Key as the surrogate key. Should I have both Project_ID and Task_ID as the business keys? Thanks.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to handle situations when there is a combined primary key in the source system?

Post  ngalemmo on Wed Aug 17, 2011 5:38 pm

If they are the business keys, then so be it. But it does seem odd. While someone may invoice a task in a project, I've yet to see a system that used that as a key. What happens if the same task is invoiced a second time?
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 handle situations when there is a combined primary key in the source system?

Post  thomaszhwang on Wed Aug 17, 2011 5:48 pm

ngalemmo wrote:If they are the business keys, then so be it. But it does seem odd. While someone may invoice a task in a project, I've yet to see a system that used that as a key. What happens if the same task is invoiced a second time?

It is because an invoice could be generated for a task or a project. A project contains zero or more tasks. Each Task and Project has a unique ID.

thomaszhwang

Posts : 32
Join date : 2011-08-11

View user profile

Back to top Go down

Re: How to handle situations when there is a combined primary key in the source system?

Post  LAndrews on Wed Aug 17, 2011 7:51 pm


Typically an Invoice line item can contain Project/Task, but the entire invoice?

If project + Task is unique for the invoice, then a vendor that supplied products/services for 5 tasks on a single project would be expected to process 5 invoices?

It does sound odd.





LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: How to handle situations when there is a combined primary key in the source system?

Post  ngalemmo on Wed Aug 17, 2011 11:22 pm

It is because an invoice could be generated for a task or a project. A project contains zero or more tasks. Each Task and Project has a unique ID..

No argument with that, but usually an invoice has its own identifier. It may refer to a project and task, but usually an invoice is an independent document.
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 handle situations when there is a combined primary key in the source system?

Post  BoxesAndLines on Thu Aug 18, 2011 9:30 am

Regardless, you need to track the identifying columns from your source data.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: How to handle situations when there is a combined primary key in the source system?

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