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?
Best Answer
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.