Wednesday, March 28, 2012

Finding Correct Primary Key

Hello,
I could not reach a conclusion on this. Maybe someone helps me to understand
better.
I have a consolidated text extract from other systems and want to upload
the data to an SQL table.
Below is the fields in extract.
OrderNo
LineNumber
SellingCompany
PurcCtry
FinalizeDt
ItemNo
CustomerID
SalesRep
Quantity
Sales_USD
At first analysis, I thought OrderNo and LineNumber are the PK of the table
and designed the table.
When I receive the data I realized that since the data is coming from
different sources. Some of them has duplicate Orderno LineNumber
combination.
Now my data has two different of type of data one has unique key of Orderno
LineNumber combination. On the other hand second part of data needs OrderNo
LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
Question is : Should I keep these data in two different table, or should I
keep them in a single table by adding more columns on PK ?
What is the affect of defining larger PK even it is not needed for some of
the data?
My table has data has 1.5 million record and increasing 75000 each month.
70 % of data has Unique Orderno Line Number
Data is used by sql server reporting services, I have also some plans to use
analysis server in the future.
Thanks in advance.
erdal,Hello Erdal,
I usually add a source system key when combining data sets from
different sources. If you add a source system key to Orderno,
LineNumber combination does that give you a unique key?
If the answer is yes then you can have a common table. I normally have
different staging tables and one common table for reporting and star
schema generation.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||Hello Erdal,
I just re-read my post and forgot to mention that I hard code or
generate the source system key. It helps me trace the source of records
in the systems I build.
Hope this is a bit clearer now,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

No comments:

Post a Comment