0

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?

2
  • It all depends.... how many entries, your budget for hardware and software or is this a thought experiment? Either way the amount of space a single table occupies versus several tables is not a determining factor in the speed of a query.
    – kevinskio
    Mar 18 '15 at 19:42
  • I don't expect the number of records to exceed a few million. Perhaps 5-6 milion max. Actually I have now a test site with 500K records using almost the 2nd design and selects are pretty fast considering im using sqlite :)) I said almost because it's missing tax_name, and now I need to group the terms inside taxonomies and I'm wondering if I should change the design
    – user61637
    Mar 18 '15 at 20:09
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.