Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2012
    Posts
    8

    Column Value Determines if other Columns will have values

    I have a table named Insurance. In the Insurance table, I have a column named Industry. The value of the Industry determines what other columns will be required. For example, if the Industry is "Technology", only the columns Exposure, Risk, and Service Type will have values. If the Industry is "Auto", only the columns Risk and Debt will have values.

    The list of industries (165) is known as well as the columns (21) that will be required to have values based on the Industry. This relationship and lists should be fairly static. The Insurance table does have other columns that are required regardless of the Industry value.

    Should I have the 21 columns (allowing nulls) in the Insurance table? Would it be better to have a separate table with the following columns Insurance ID, Field Name & Field Value where the value of Field Name is one of the 21 column names? Or is there a better way to design this?

    Thanks in advance for your help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by logicg8 View Post
    Should I have the 21 columns (allowing nulls) in the Insurance table? Would it be better to have a separate table with the following columns Insurance ID, Field Name & Field Value where the value of Field Name is one of the 21 column names? Or is there a better way to design this?
    this suggests three solutions -- single table as is, the entity-attribute-value (EAV) approach, or a better way, which in this case would be the supertype/subtype model

    of these three, EAV is by far the worst

    i suggest a single table, because you said things were fairly static

    do some research on supertype/subtype and see if you don't agree
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Single table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2012
    Posts
    8
    Thanks for the replies. I researched EAV to better understand why it is not a good solution to the problem. I also looked at supertype / subtype and felt that it did not represent the model well.

    I've decided to go with a single table where the columns that don't apply to the record will just be null. This also seems to make things much easier on the application (programming) side. Thanks again for the feedback.

Tags for this Thread

Posting Permissions

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