Alex Rivera | Logout

Deciding between an artificial primary key and a natural key for a Products table

Asked 2009-02-26T13:02:52.340
24

Basically, I will need to combine product data from multiple vendors into a single database (it's more complex than that, of course) which has several tables that will need to be joined together for most OLTP operations.

I was going to stick with the default and use an auto-incrementing integer as the primary key, but while one vendor supplies their own "ProductiD" field, the rest do not and I would have to do a lot of manual mapping to the other tables then to load the data (as I would have to first load it into the Products table, then pull the ID out and add that along with the other information I need to the other tables).

Alternatively, I could use the product's SKU as it's primary key since the SKU is unique for a single product, and all of the vendors supply a SKU in their data feeds. If I use the SKU as the PK then I could easily load the data feeds as everything is based off of the SKU, which is how it works in the real world. However the SKU is alphanumeric and will probably be slightly less efficient than an integer-based key.

Any ideas on which I should look at?

database primary-key
Edit
Report

2 Answers

1

A surrogate key (auto increment INT field) will uniquely identify a row in the table. On the other hand, a Unique Natural key (productName) will prevent duplicate product data from entering the table.

With a unique Natural key field, two or more rows can never have same data.

With a surrogate key field, Rows can be unique because of the auto increment INT field but data in rows will not be unique because the surrogate key has no relation to the data.

Lets take an example of a User table, the table's Natural key field (userName) will prevent same user from registering twice but the auto increment INT field (userId) will not.

answered 2013-04-10T15:44:12.820
0

If every product will have a SKU and the SKU is unique to each product, I don't see why you wouldn't want to use that for a possible primary key.

answered 2009-02-26T13:06:05.827

Your Answer