Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Question The best way of storing a set of string values

    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

    .

  2. #2
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    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.

  3. #3
    Join Date
    Dec 2003
    Posts
    14
    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

    .

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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:
    Code:
    ProductID  Type
    ---------  ----
    1234       TV
    1235       T.V.
    1247       tv
    1278       T V

  5. #5
    Join Date
    Dec 2003
    Posts
    14
    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

    .

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2003
    Posts
    14
    Thanks Rudy. I always enjoy your guides and comments.
    I have learned a lot by this thread.

    Thank you all.
    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •