Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    3

    Unanswered: What's the best way to represent unknown number of properties ...

    I'm trying to design a schema for my database, and I'm stumbled at this problem. I really would like to use a multi-dimensional database for this, but that's not an option - I have to use MySQL.

    I have a bunch of objects that could have an X number of properties. So an object O can have property1, property 2, property3, etc.. There can potentially be about a 100 of those. So what would be the best way to represent it in a database?

    Having a column for each of those property, and just putting a NULL when an object doesn't have this property doesn't seem like the best idea. 100+ columns in your table is never a good idea, imo.

    Googling for it produced no results, maybe because I don't know what's the best way to describe it, so don't really know what search terms to put there.

    Has anyone encountered this problem before? Is there an official name for it? What's the best and the most efficient way to solve this?

    Thank you!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create a table of properties, and load the 100 rows

    create a table of objects

    then create a table of objectproperties, such that there is one row for each object/property combination

    the next question is, should the objectproperties table have, besides its two foreign keys, one numeric column, one character column, one datetime column, etc. (basically, one column for each datatype), which will mean that all but one of them will be null

    or should there be an "objectproperties" table for each datatype?

    that degree of generalization is up to you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2003
    Posts
    3
    Thanks!

    I was considering that. The only thing that makes me doubt this solution is wouldn't that table get really large fairly soon? If I have 10,000 objects, each of them having, on average, 50 properties, the ObjectProperties table would have 500,000 rows. Can MySQL handle that? Is performance going to be ok?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, mysql can handle that

    500,000 is a small table

    500,000,000 is a large table

    500,000,000,000 is a very large table

    performance will depend on proper indexing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2004
    Posts
    23
    I like the simplicity of the forum.
    I would like to see the green in that header image caried out throughout the rest of the layout though, then it woulc fit in a bit more.
    SnapSlides.Com - Create slideshows online to share with friends and family.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by jasong
    I like the simplicity of the forum.
    I would like to see the green in that header image caried out throughout the rest of the layout though, then it woulc fit in a bit more.
    could you maybe explain which forum you're talking about?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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