There’s a few general pros and cons I can think of, there are situations where one is better than the other:
Option 1, EAV Model:
- Pro: less time to design and develop a simple application
- Pro: new entities easy to add (might even
be added by users?) - Pro: “generic” interface components
- Con: complex code required to validate simple data types
- Con: much more complex SQL for simple
reports - Con: complex reports can become almost
impossible - Con: poor performance for large data sets
Option 2, Modelling each entity separately:
- Con: more time required to gather
requirements and design - Con: new entities must be modelled and
designed by a professional - Con: custom interface components for each
entity - Pro: data type constraints and validation simple to implement
- Pro: SQL is easy to write, easy to
understand and debug - Pro: even the most complex reports are relatively simple
- Pro: best performance for large data sets
Option 3, Combination (model entities “properly”, but add “extensions” for custom attributes for some/all entities)
- Pro/Con: more time required to gather requirements and design than option 1 but perhaps not as much as option 2 *
- Con: new entities must be modelled and designed by a professional
- Pro: new attributes might be easily added later on
- Con: complex code required to validate simple data types (for the custom attributes)
- Con: custom interface components still required, but generic interface components may be possible for the custom attributes
- Con: SQL becomes complex as soon as any custom attribute is included in a report
- Con: good performance generally, unless you start need to search by or report by the custom attributes
* I’m not sure if Option 3 would necessarily save any time in the design phase.
Personally I would lean toward option 2, and avoid EAV wherever possible. However, for some scenarios the users need the flexibility that comes with EAV; but this comes with a great cost.