Hi all,

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.

DB
----------------------
PRODUCT table
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.

DB
--------------------
PRODUCT table
||
||
TYPE table
1 CD player
2 MP3 player
3 TV
4 Radio



======= 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

DB
----------------------
PRODUCT table
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.

Any help and comment is appreciated.

Thanks
Sam

.

IMHO it would be best to have something like:

Product(ProductID, TypeID,...)

Type(TypeID,TypeName)

And join for reporting. However, fewer joins tend to improve reporting performance.

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.

Sam

.

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:

Thank you very much Tony for your explanations. They were what I wanted to hear.

I was thinking whether mentioning the name codes in documentation is enough or not. Now, I know it is not the proper way.

Also, I didn’t note that by option 3, putting all types in one column, I would lose the CHECK option for ‘each’ of them.

Again, I appreciate certus and your help.

Sam

.

you could still have the CHECK constriaint

... CHECK( type in ('TV','VCR','MP3','CD','radio') )

however, a new type then requires a structural change (typically never implemented via application code to be issued by users)

Thanks Rudy. I always enjoy your guides and comments.
I have learned a lot by this thread.

Thank you all.
Sam