Results 1 to 4 of 4

Thread: design question

  1. #1
    Join Date
    Dec 2009
    Posts
    27

    design question

    Hello All,

    We currently have 2 objects with properties: casino and bank
    We want to define propeties for each. So i came up with this design:

    casino (id, name) = date: (111,'mycasino')
    banking (id, name) = data: (222,'mybank')

    object_type (id, name) = data: (1,casino), (2,bank)
    object_type_propety (id, object_type_id, name, desc) = data: (10,1,casino_url,<desc>), (20,2,bank_url,<desc>)
    object_type_value (id, object_id, property_id, value) = data: (100,111,10,'http://casino...'), (200,222,20,'http://bank...')

    the idea to allow diffrent properties to each object or to future objects: casino, bank.

    Q: will it be better to design a DWH style table for each object
    where property will be a column ? therefore reading one table for each object
    OR will it be better to create this table module for future properties ?

    it's more question of using alter table add column to add new properties, or
    use this module, where i can simply insert new object and add it's properties...

    thanks
    Chanan

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you just (re)invented the entity-attribute-value (EAV) model

    do some googling and you will find that this model makes it very simple to store data, but exceedingly complex to retrieve data in any meaningful way

    unless you are a very experienced database and application developer, the advice is not to try this, but instead to define ordinary tables in the ordinary way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    27

    design question

    Hi,

    Thanks for the quick reply.
    It's a matter of working with multiply tables vs. working with one DWH style table - which will be faster. Assuming my data is most likely to be static
    (alot more reading then updates).
    But still the data will not consist of too many records, each one not more than 1k value records.

    But then everytime new property will appear, i would need to add a new column. and everytime a new object added, means more tables.

    I am more worried about performance, when reading from multiple tables (although indexed), then reading a full table for each object.

    Chanan

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bchanan View Post
    I am more worried about performance
    in that case, stay well clear of EAV
    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
  •