L' Eomot Inversé (10/4/2012)
ScottPletcher (10/3/2012)
Typically customer codes are sequential. Since they're not here, that would definitely make the clustering key choice much more difficult.Typically customer codes occur in order records more often than in customer records, and while they may be sequential in order of creation of customers they are certainly not sequential in terms of creation of orders. So the ideathat a customer code is typically sequential in relation to whatever table we are talking about here is not reasonable.
just like a products table... every product has a product code which is used in searches but the inserts are never sequential and usually they are strings.. so a surrogate key would apply very well also in this case..
natural keys are "beautiful" but very hard to determine what columns should be... can be customer code but also the ITIN since every person has one but usually you don't give your number before you make a purchase, when you initially register the customer...
One question though...
Suppose I have a Products, a Warehouses, a WarehouseProducts, Orders and OrderProducts and the OrderProducts is "linked" to WarehouseProducts, since I can make an order to my suppliers for the same product but to different warehouses (on the same order).
Products has ProductCode, Warehouses has WarehouseCode, WarehouseProducts has ProductCode and WarehouseCode and OrderProducts as ProductCode and WarehouseCode.. these are all strings... Isn't it better to add surrogate keys IDENTITY to all tables and make the "link" by it?
Pro: fast in joins, less columns on "linked" tables...
Cons: If I want to get the product description from an order I have to use the WarehouseProducts to get to the Products table.. (one more join...). - or could simply add the ProductId along with the WarehouseProductId on the OrderProducts table but that seems "redundant"...
Thanks,
Pedro