EAV table question

Question

What's the best design for taxonomies in EAV tables?

taxonomy
  id autoincrement
  name unique

terms
  id autoincrement
  tax_id references taxonomy(id)
  name text unique
  value text

relationships
  id autoincrement
  term_id references terms(id)
  post_id references posts(id)

vs a single table:

relationships
  post_id references posts(id)
  tax_name text
  term_name text
  term_value text

I think that if I use a single table the db will use slightly more space, because it will store the tax name and term name for each record (relationship). But would it be faster when performing queries that select records based on multiple terms and term values?

Answer 1

Take a look at what Joe Celko has to say about EAV (AKA OLTP and also MUCK - Massively Unified Code Key - there's a reason that he chose that name!). Avoid this at all costs.

However, if you wish to persist in this folly, then download and look at Magento, a commercial (and partially F/LOSS) implmentation of the EAV paradigm.

Storing a value as text is just not the way to go - it messes up query optimisers for starters - and that's just the beginning of the problems.