I wonder which of the options at the bottom are more standard and acceptable by experienced DB designers. (And why?)
If we have a database with its web application, and there is an entity which one of its properties has a set of specific string values, for example, PRODUCT entity has property TYPE which has 4 string values 1)CD player 2)MP3 player 3)TV 4)Radio.
Which option is better?
======= Option ONE
We assign a numerical or string code to those string values and
design our application to convert the users' data entry to those codes and
enters that code into the TYPE column of the PRODUCT table.
When we need reports, our application reads those codes and converts them to the original string values.
TYPE column ---- 1 -- 2 -- 3 -- 4 <- - API - -> -CD player -MP3 player -TV -Radio
======= Option TWO
We create a separate table for TYPE and
assign Type ID for those string values and
relate the TYPE table to PRODUCT table.
When we need reports, we join the tables and read the complete value names.
1 CD player
2 MP3 player
======= Option Three
Simply, we keep the TYPE property inside the PRODUCT table and
enter the string values into the TYPE column by their full name
TYPE column ---- -CD player -MP3 player -TV -Radio
I have many of similar situations. I'd like to know that which one is the most efficient way for storing the predifind set of string values.
Thanks a lot Certus for your reply. So option 2 is preferable.
I thought storing the values in the entity table with their full name (not coded) [option 3] might save me some processing time compare to option 2 (joining tables) and option 1 (translating coded names by application).
Apparently, those are not worth it to ignor the common methods.
Option 1 is something you should never do: the data is meaningless without the application to interpret it.
Option 2 (as Certus recommends) is good.
Option 3 could also be good, if the type names are simple and not subject to frequent changes. But you would still need your separate TYPE table to validate the entries (via a foreign key) - or perhaps a CHECK constraint: otherwise you could end up with data like:
1278 T V