Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    2

    MySQL: Entitie-List with up to unlimeted 'properties' of diffrent datatype

    Hello all together,
    i ran into a problem about database design i don't know to solve on my own anymore, as of this i'd like to consult you

    Background information:
    I have a list of entities* wich may have an increasing (so up to unlimited) amount of properties.
    * The list of entities may change as of addition and deletion of entities..

    The properties have diffrent datatypes (basicly text or numbers) and in somecases there are additional information such as notes concerning the value or an date of the value. (~ "Multifield Properties" -.- )

    In some cases there is no property value available for a specific entity.



    Query requirements:

    Get a result of selected/all entities with selected/all properties. (the query itself might be generated by some script that knows the list of entities and all available properties).

    So ideally a result structure would be:
    Code:
     
    +--------+-----------+-----------+-----+-----------+
    | entity | property1 | property2 | ... | propertyn |
    +--------+-----------+-----------+-----+-----------+
    | ...... | ......... | ......... | ... | ......... |
    | ...... | ......... | ......... | ... | ......... |
    My attempts/thougts so far:

    1)
    I could put everything into one table, however due to the many properties i may run out of fields, and with the mentioned extra information it will get quite messy.
    + easy to query
    + no datatype issues
    - limitation in fields


    2)
    The second possibility would be to have an extra table for each property or group of properties, that would be kinda comfortable to query with simple joins, but gets messy on the tables side, and is basicly the first structure splitted into multiple tables :/
    + no datatype issues
    = meets the "all entities with selected properties" requirement with almost plain sql
    - lots of tables




    3)
    What i ended up for now, is a three table structure:

    tbl_entities
    fields: eid, name

    tbl_properties
    fields: pid, property, datatype

    tbl_values
    fields: vid, pid, eid, dbl_value, txt_value, note_of_value, date_of_value

    However, however at this point of have absolutly no idia for an query to achieve something that comes close to the desired result.



    So what i am after, is an DB design that meets requirement, but stores the data as effezient and normalized as possible :/

    Thanks =D
    Last edited by tumbulunka; 01-06-09 at 17:15.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Relational database are designed to enforce business rules and maintain relational integrity. They are not intended to allow any user to put any data anywhere they want and create schematic modifications as a function of transaction processing.
    That is what Excel is for.
    If you must have customizable data, add an XML column and load it up with whatever you want.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jan 2009
    Posts
    2
    mhm intresting point but i wanted to take advantage of mysql for doing statistics for example :/

    however i just learned something huge in mysql.. using subqueries ^^

    so.. attempt 3) is allright.. as i can get an result that joins the entity table

    thanks! =]


    (might be closed as problem is solved

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    tbl_entities ... tbl_properties ... tbl_values
    Rather than EPV it's normally called EAV though it's hated with a vengeance on here on here but you might want to look it up. It can be improved by adding relational integrity etc but any design using EAV will have inherent weaknesses. If you want to see an example system that has those type of features. There was a heated thread that covered all the cons and a few pros of using EAV. Everything that was raised probably applies to any method used to meet your requirement (including XML).

Posting Permissions

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