I've got a "variable" structure to become include a table DB. By "variable" I am talking about a sequence of couples area/value where the "kind" of area determines the worthiness type, I'm not sure exactly area order and I'm not sure the number of occasions fields can repeat. Sometimes number of fields will repeat several occasions (it's a fiscal model).
Additional requirement: I ought to map these variable data into web site forms, handling some CRUD work. JQuery-ui, Struts 2, Hibernate. Preferred DBMS: MySQL.
The solutions I figured of:
- vertical table. I possibly could possess some performance problem, that we could resolve with materialized sights that "pivot" the rows in posts after i need massive data process. Not gone to date within this direction because it appears to be really costly for development.
- LOB fields. Pack my posts into among individuals, possibly getting a "mapping" table to decode each column. My idea would be to pull-out searchable fields as "real" posts to be able to leave within the LOB only the less interesting mob of information and never to create performance problems.
- or better 2a. Make use of an xml within the LOB area. This may be helpful to bring alongOrunpack data more easily, specifically needing to map data to some web form.
What is your opinion? And much more, can there be a way to produce automatic sights from xml fields? Or easier to map such data to web form? I suspect Hibernate Tools will not work most of the cases I referred to.
I really hope I've been obvious, will still be a little confusing even going to me :)
Your option 1 may be the Entity-Attribute-Value antipattern.
See my response to Product table, many kinds of product, each product has many parameters and my blog publish EAV FAIL for options and several reasons why EAV is wrong, a minimum of for any relational database (I cover EAV in my opinion, SQL Antipatterns: Avoiding the Pitfalls of Database Programming).
Also look at this article about how exactly an identical structure nearly condemned a business: Bad CaRMa.
Your choices 2 &lifier 3 act like referred to in How FriendFeed uses MySQL to store schema-less data. I'm not sure associated with a automatic method for an ORM to keep that structure for you personally. You have the chore of keeping your inverted index tables synchronized together with your LOB data.