CODEWITHSUNDEEP
databasedatabase-design
user1016313
user1016313

Reputation: 1314

ProductID vs barcode in database

I am building a database for a commercial store. I am debating the use of productID in the DB.

I also need to store the transaction history in the database.

Would it be better to use the productID, or use the barcode as a unique identifier for my products.

Upvotes: 0

Views: 709

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52127

You basically have two options:

  1. Having just the natural key (barcode) in the PRODUCT table.
  2. Having both natural (barcode) and surrogate key (id) in the PRODUCT table.

In the first case:

  • The PRODUCT table will be "slimmer" (since there is only one index). There will be less index maintenance and caching will be more efficient.
  • The barcode will be migrated to all child tables1, so you won't have to JOIN with PRODUCT to get the barcode.
  • But for the very same reason, child tables will be "fatter" (since barcode is fatter than id). They will take more space and be cached less efficiently.

In the second case:

  • The PRODUCT table will be fatter (since there is the additional field and index on top of it).
  • The child tables will have to be JOINed with the PRODUCT to get the barcode.
  • But the child tables will be slimmer.

So it's basically a tradeoff between less or more JOINing and between "bottom-heavy" or "top-heavy" storage/caching. You just need to decide which fits better with your anticipated data access patterns...

1 I.e. tables that reference PRODUCT through FOREIGN KEY. A TRANSACTION_HISTORY might be an example of one such table.

Upvotes: 2

nvogel
nvogel

Reputation: 25534

You can use both can't you? No reason why products shouldn't have more than one type of identifier. Depending on circumstances however, a product could have more than one barcode. For retail packaged goods it's not uncommon to have multiple different barcodes for the same product.

Upvotes: 4

HLGEM
HLGEM

Reputation: 96600

Barcodes in general would be a bad PK. You would not want to join on them due to the datatype they would be stored as. Product Id is probably a better choice for any table that will have child tables. However, you should probably still put a unique index on the Barcode, nothing prevents more than one thing from having a unique index.

Upvotes: 2

Related Questions