Loading ...
Sorry, an error occurred while loading the content.
 

Re: OT: OO data design and search performance

Expand Messages
  • Carl Holm
    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
    Message 1 of 12 , Apr 11, 2003
      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.

      Regards,

      Carl Holm

      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.
      >
      > regards,
      >
      > A.
      >
      >
      >
    Your message has been successfully submitted and would be delivered to recipients shortly.