Re: OT: OO data design and search performance
- I would second this approach. Nothing that you have described about
your data modeling task would preclude the
use of a fully normalized relational DB. Once you have this built,
performance can be improved through the creation of
indexes (where applicable) and by optimizing the SQL.
Aaron Trevena wrote:
> Clinton Gormley wrote:
>> [ snip]
>> So with an advanced search, you may want to specify a place to stay
>> which is budget, has air-conditioning and a vegetarian restaurant.
>> It is clearly possible to build a search which ploughs through all of
>> the tables, but it would be much faster if I had one flat table for
>> (eg) places to stay with all of the attributes in different columns.
>> My original plan was to do just this - use a cron job to rebuild the
>> flat tables when any objects are changed. But this negates the
>> flexibility of the design ie instead of being able to add and remove
>> attributes by altering a single list, I now also need to change the
>> structure of the flat tables and the SQL which accesses them
> You do not need to denormalise - Your objects should have a many to
> many relationship with attributes, like being vegetarian. This many to
> many relationship maps to a lookup table containing unique records of
> attribute id and object id.
> This has many advantages - extra attributes are easy to add, you have
> a normalised data structure and you have a reverse index that requires
> only a very very simple join (join attribute table to lookup table and
> object table by attribute and lookup id respectively). This join will
> make good use of indexes for these tables and be pretty responsive.
> the data (or rather the relationship) is only stored in one place and
> therefore is always up to date and you don't have to worry about data
> being incorrect or out of date.
> You very rarely need to flatten tables - good normalisation means you
> can have a decent design and still have queries that perform at a
> decent speed.