#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Posts
    449
    Rep Power
    80

    database structure for many attributes


    Need some advice planning my database structure. I have about 1,000 products but its not a store. Currently my product table has 60 fields.I am about to add some more.

    Since some categories of products will not use all the entries is it ok to have many empty fields? Not every product will have a 'size' for example. Is there a more efficient way to store this data?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,779
    Rep Power
    4289
    Originally Posted by mallen
    ... is it ok to have many empty fields?
    yes

    Originally Posted by mallen
    Is there a more efficient way to store this data?
    yes, it's called EAV -- entity-attribute-value

    it's very efficient for storing all kinds of different attributes, but please BE WARNED it's a ~bitch~ to query... i mean, you'd think a table with only 3 columns would be easy to query, but to extract any meaningful data means re-assembling the various pieces and you will go nuts trying to do it, and do it efficiently

    just google it to get a sense of how much people hate it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo