1

I have a "variable" structure to be put in a table DB. By "variable" I mean a sequence of couples field/value in which the "kind" of field determines the value type, I don't know exactly field order and I don't know how many times fields can repeat. Sometimes group of fields will repeat several times (it is a fiscal model).

Additional requirement: I should map these variable data into web page forms, handling some CRUD work. JQuery-ui, Struts 2, Hibernate. Preferred DBMS: MySQL.

The solutions I thought of:

  1. vertical table. I could have some performance issue, which I could resolve with materialized views that "pivot" the rows in columns when I need massive data process. Not gone so far in this direction as it seems to be very expensive for development.
  2. LOB fields. Pack my columns into one of those, perhaps having a "mapping" table to decode each column. My idea is to pull-out searchable fields as "real" columns in order to leave in the LOB just the less interesting mob of data and not to generate performance problems.
  3. or better 2a. Use an xml inside the LOB field. This could be useful to pack/unpack data more comfortably, specially having to map data to a web form.

What do you think? And more, is there some way to create automatic views from xml fields? Or better to map such data to web form? I suspect Hibernate Tools won't work in any of the cases I described.

I hope I have been clear, it's still a bit confusing even to me :)

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Ema
  • 104
  • 11
  • 1
    I have used 1 and it is a pain even with materialized views. They become huge and prone to errors(DBA used ='SIBLING' instead of ='Sibling' in one of the conditions). – Stefan Dec 20 '11 at 18:58

1 Answers1

0

Your option 1 is the Entity-Attribute-Value antipattern.

See my answer to Product table, many kinds of product, each product has many parameters and my blog post EAV FAIL for alternatives and some reasons why EAV is wrong, at least for a relational database (I cover EAV in my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming).

Also read this article about how a similar structure nearly doomed a company: Bad CaRMa.

Your options 2 & 3 are similar to described in How FriendFeed uses MySQL to store schema-less data. I don't know of any automatic way for an ORM to maintain that structure for you. You do have the chore of keeping your inverted index tables in sync with your LOB data.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I wouldnt call it an antipattern. The Wikipedia article you linked lists use cases where EAV makes perfect sense. – Stefan Dec 20 '11 at 19:01
  • Except that you lose many features of SQL, such as datatypes, constraints, and a table heading. You have to implement metadata yourself in application code, instead of relying on the RDBMS to enforce structure. – Bill Karwin Dec 20 '11 at 19:17
  • I agree on these. But you have similar problems with XML in columns which are even worse when it comes to reports. There are valid usecases for EAV and it does not qualify for antipattern in my opinion. Its like weakly and stronlgy typed languages. Both have their (different) uses. – Stefan Dec 20 '11 at 19:28
  • Agreed; the problem is storing non-relational data (variable number/type of fields) in a relational database, and trying to use SQL to work with that data. It's bound to be awkward no matter how you do it, which is why it's such a common question here on SO. – Bill Karwin Dec 20 '11 at 19:51
  • thank you both for answers and insights. On one side you confirm what I was fearing (deadly consequences of EAV in handling data) but, on the other side, I understand it will be a mess any solution I choose. I don't want to have a schema-less db, anyway, as only a part of my data is dynamic. – Ema Dec 21 '11 at 11:21