8

I've been doing a ton of research on database design for an upcoming project.

This is the classic inner platform problem, where our client basically wants infinite customization and the ability to create forms and attributes on an entity, collect them values from end users, and be able to display the collected information on graphs.

In will be something used by the clinicians to help monitor patients, and why even using the EAV is a thought is that we'll need to collect different information for different trial runs. Sometimes it might be what they ate that day. Others it might be blood sugar, or blood pressure(which is really two numbers), and othertimes it might be multiple questions (how is your pain today from 1-10?), all with the idea that we'll never really know in advance what exactly the end client will be asking for, or really what the accepted values will be.

We'll also be graphing this data consistently throughout the program, and generating larger reports on a less regular basis.

Ideally I'd like to able to hard code as much of this as possible, as we are using SQL, and sticking to relational database best practices will simplify both the database design and the application design (both of which I'm writing).

We're doing a few trial runs, and my first inclination is to get as much information as possible from the cients, hard code the tables in the database, and then build from there. If we discover that we NEED to use an attribute table and an attribue_value table to collect those attributes (along with the fun-to-implement form builder things like dropdowns - and thus dropdown menu options and validation/required), we could do so for later launches.

I've gone through basically every relevent stack overflow post; most say avoid EAV, get a better understanding of the requirements of the application, and, at some point, if the customer TRULY needs an EAV implementation, to go ahead and do it then.

  • Has anyone ever used a hybrid model? Can you discuss it?

  • Has anyone ever successfully implemented the EAV model, and can you discuss it?

  • Has you been in a similar decision, decided to not implement EAV for a project that seemed like it might have been a candidate? How did that turn out?

Here are some interesting reads I've found along the way:

http://decipherinfosys.wordpress.com/2007/01/29/name-value-pair-design/ Storing time-series data, relational or non? Database EAV Pros/Cons and Alternatives Alternatives to Entity-Attribute-Value (EAV)?

And the link that really gave me a ton of insight.

Community
  • 1
  • 1
Squadrons
  • 2,467
  • 5
  • 25
  • 36
  • 2
    [More food for thought](http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back) - start at slide 16 for EAV discussion. – Benny Hill Aug 29 '13 at 20:51
  • 3
    See also my presentation [Extensible Data Modeling](http://www.slideshare.net/billkarwin/extensible-data-modeling) for pros and cons of different alternatives. – Bill Karwin Aug 30 '13 at 21:03
  • To anyone else coming to this - I read both of the links in this comment thread. They are both very well written, informative, and highly recommended if you're treading this path. – Squadrons Feb 28 '14 at 15:55

1 Answers1

0

After some thought, and considering the clients needs/requests, using an EAV model was the correct answer here.

After doing some more research I decided to use Postrgresql and make full use of its HSTORE data type, which allows storing, searching, and indexing of key value pairs in a single field.

Here is a paper benchmarking hstore vs EAV: http://wiki.hsr.ch/Datenbanken/files/Benchmark_of_KVP_vs.hstore-_doc.pdf

The paper above benchmarks hstore vs an EAV table, and hstore came out way ahead.

Another option we considered was having a task table that covered all the bases:

id, name, value_1, value_2... note_1, notes_2

Obviously the thought of that killed me inside a bit, so I was either going to use a task_type attribute table:

a task is prescribed by an administrator to a user and has a task_type, the task_type_attributes are for all tasks of that type (ie, define that for a exercise task, we want to be able to store information about the intensity of the exercise, the time the exercise took etc).

Once the user brings up the task, they see the task_attributes as fields to fill out. They enter these fields, and the attribute_value they enter are then associated with the task_entry of the patient (which also states if they completed it, skipped it, etc)

task_attributes

  • id
  • task_type_id
  • attribute
  • attribute_value_type (for generating the desired fields on the app side - ie, knowing to have a dropdown vs a text input)
  • min_value
  • max_value
  • required

tasK_entry_values

  • task_entry_id
  • task_type_attribute_id
  • value

Hope this might be of use to someone. I'd also be interested in any and all criticism/feedback for this design.

Squadrons
  • 2,467
  • 5
  • 25
  • 36
  • 1
    Just make sure you load test this with test data that will be roughly the size of the database in about a year. You really don't wnt to find out then that you have to restructure. – HLGEM Aug 30 '13 at 18:43