April 29, 2009 at 8:38 am
Hi there,
I have designed a look up table between a product and sku table and had a combined key sku_ID, product_ID. Due to this when Im trying to establish the new relationship between other tables I need to specify both the key. Is it good to remove pf_ID from being a key?
Table are structured as below
Product Table
product_ID -PK
product_name
product_info
Look Up table
SKUProductLookUp
sku_ID -PK
product_ID -PK ( is this needed to be PK?)
If I need to introudce any new relationship with SKuProductLookup Table im forced to include a column that will refer this key.
Any suggestion will be helpful
ta
April 29, 2009 at 9:12 am
I don't have enough data to be sure, but the way you currently have it looks correct, or at least standard.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 30, 2009 at 1:31 am
Here are my table scripts.
CREATE TABLE Product
(
product_id varchar(20), --- Primary key
product_name varchar(30)
Constraint PK_product_id Primary Key
) ON Primary
Create TABLE Sku
(
sku_id varchar(20),
product_id varchar(20),
Constraint PK_Sku Primary Key
(
sku_id , product_id
)
Constraint FK_product_ID Foreign Key
( product_id)
References Product(product_id)
)
Now if I need to create a new realtionship with Sku with anyother table I need to inherit these two keys. Since there is no way to identify the column uniquely using sku_id I considered a combined key. is this approach correct ?
April 30, 2009 at 1:47 am
vidhyasudha (4/30/2009)
Here are my table scripts.CREATE TABLE Product
(
product_id varchar(20), --- Primary key
product_name varchar(30)
Constraint PK_product_id Primary Key
) ON Primary
Create TABLE Sku
(
sku_id varchar(20),
product_id varchar(20),
Constraint PK_Sku Primary Key
(
sku_id , product_id
)
Constraint FK_product_ID Foreign Key
( product_id)
References Product(product_id)
)
Now if I need to create a new realtionship with Sku with anyother table I need to inherit these two keys. Since there is no way to identify the column uniquely using sku_id I considered a combined key. is this approach correct ?
Yes, this approach is quite perfect. However, I was not clear about what's the exact nature of your problem: When you do need to create a relationship between another table and the sku table, you will need to use the the primary key of sku table (sku id and product id) as a foreign key in the table you are seeking to relate the sku table with.
I don't see there's a way of avoding that.
Alternatively, you can also define a completely new identity column to serve as a primary key for your sku table. In which case you will NOT need to use the composite key as primary key and you will have obviated the need to use these while further defining relationships between SKU and any other new table. However, depending on your unique business requirements, you may still need to maintain the foreign key relation of the product field in SKU with the product field in the product table.
Regards
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537
April 30, 2009 at 2:56 am
Thank you. Your explanation cleared my confusion.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply