4

Most of our tables in our MySql db are not EAV tables. I've been reading SQL Antipatterns and am trying to determine the best way to redesign this table. I know we would love the benefits of easier references in our spring forms and better data integrity.

The EAV table has optional attributes that may come with an expense. In the code there are 55 options right now and that number keeps growing. The average number of attributes per expense is 2.719 and the most a single expense has used is 39 attributes.

Would creating a table with 55+ options be ideal in this situation or would the next person who has to maintain this cringe at the size of the table?

Ann Addicks
  • 915
  • 12
  • 25
  • 1
    If you notice that only certain subsets of options make sense together then you could refactor those into subtables using one of the many forms of table inheritance. – Hiro2k Mar 21 '12 at 15:09
  • Thanks, I've thought about that, but each time I start its a daunting task because so many expense types may or may not contain fields. I may just have to sit down and write it out to see if it will work well. – Ann Addicks Mar 21 '12 at 18:39

1 Answers1

3

This sounds like a good time to look into document-oriented storage as an alternative (non-SQL) design, where the EAV triples can be stored as e.g. JSON data in MongoDB. You don't define a schema for this, so the query syntax is different from SQL, but it can be far more powerful in some cases. For example, I saw a presentation recently on using this sort of setup to make a linked-data store using RDF, which then formed a common layer underpinning a whole load of other services e.g. REST, webservices, XMLRPC etc. Far easier to do this with the data all standardised apparently.

See this question for some more thoughts on whether this would be a good idea.

Community
  • 1
  • 1
Matt Gibson
  • 14,616
  • 7
  • 47
  • 79