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

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

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

in that case, stay well clear of EAV