5

Does it make more sense to store and retrieve properties or information directly related to an item in a database, or, say in such a case that a product's ID could describe information about it, should the information be gathered from that?

Example: Item SKU -- 4HBU12

4 - is the number of motors
H - the voltage
B - the color, blue
U - the model
12 - the length

Should I store those individual attributes as well as the SKU, or should I store only the SKU and build the attributes from it?

  • Consider doing both. The SKU is like a invoice number. While in concept state it might change. When it is being used (when invoice is sent) the invoice number cannot change anymore ever. Maru de Vera noted the right reasons to have attributes but in this case you need both likely. – Luc Franken Oct 21 '13 at 20:04
17

It would be better to store the individual attributes of the product and store the SKU as well.

This is in order to allow more flexibility in the way you design your attributes as well as to allow you to query specific products more efficiently through SQL. If relied on the SKU to derive the attributes of the products, its going to be really hard to write select * from item where voltage = 'H'.

While one may argue that you can simply do a wildcard query to do the searching for you, this makes it hard for you to extend your database in the future. For example, after a year or two, you now have to use two characters for your voltage attribute instead of one, effectively all queries built with a wildcard query like this select * from product where SKU like '_H__%' will not get you the right result all the time.

4

Putting reporting aside.

Store the Individual Attributes... Heres why, What if a mistake is made?

Suppose you manufacture 5000 black units then realize that they have been made red accidentally? What do you do?

You could have pre-orders and receiving history and this SKU can exist on a 100 tables in the system before you realize it. Even if you can change the SKU throughout the system, what about all the paperwork, such as PO from customer\vendors (It may even be printed on the product itself!). As a general rule a key should never define attributes. Naming conventions are great, but should be just that a convention, not a hard system enforced rule.

2

Two problems with your data encoding scheme that I can see right off the bat are:

  • How do you represent a Black or Brown one now that Blue is used?
  • What if you now need to make a 4HBU12 that's designed to fit in a Honda and one for every other make of vehicle?

I typically try to not work special meanings into identifiers for these and other reasons.

However it does somewhat depend on what you intend to do with the number. In my experience it can be useful for users to be able to 'predict' what a product number would be without needing access to a computer. Also if you have these products floating around with model number labels on them, if the model number is the only real visible indicator of what's in the box, this may again support the exception.

So, while I avoid allowing identifiers to have special hidden meanings, I understand that as with most things, there are exceptions.

  • That was just a made-up example for the sake of helping my question be a little more understandable. – Nathan Lutterman Oct 21 '13 at 21:03
  • So was mine. The concept still applies. I was just playing into your example. – Wes Grant Oct 21 '13 at 21:18
2

I would suggest considering to store just the SKU.

I have found that any setup that stores both is ripe for them getting out of sync in the long term and the problems it causes can be devious.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.