7

I'm interested in playing around with the EAV model over an SQL Server Database. Is anybody able to point me to an article or framework for an existing model/schema (I'm not intrested in Saas for this)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jaimal Chohan
  • 8,530
  • 6
  • 43
  • 64

4 Answers4

17

Best Practices for Semantic Data Modeling for Perfor...

EAV is notoriusly problematic as it leads to severe deployment performance and scalability problems. The Whitepaper in the link, released by the SQL Server Customer Advisory Team tries to offer some guidance to deploy a succesful EAV model.

Byron Whitlock
  • 52,691
  • 28
  • 123
  • 168
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
5

I haven't used it, but there's an example EAV structure with data in this codeplex project:

http://eav.codeplex.com/
"A sample EAV pattern for SQL Server with: Tables and indexes, Partial referential integrity, Partial data typing, Updatable views (like normal SQL table)"

Provides some SQL scripts to download, here.

Also, this blog post: "dave’s guide to the eav"
http://weblogs.sqlteam.com/davidm/articles/12117.aspx

... works through an EAV example in SQL Server with examples of how type checking and querying would be implemented, with lots of discussion about the theory and why EAV can cause problems.

codeulike
  • 22,514
  • 29
  • 120
  • 167
2

Why not create a very simple table with an entity column, an attribute column, and a value column:

CREATE TABLE eav(
    entity STRING,
    attribute STRING,
    value STRING);

And then populate the table using a script of some sort to generate some test data.

INSERT INTO eav ('banana','color','yellow');
INSERT INTO eav ('banana','fluffy','no');
INSERT INTO eav ('banana','origin','Guatemala');
INSERT INTO eav ('orange','origin','USA');
INSERT INTO eav ('orange','origin','Mexico');
INSERT INTO eav ('pear','origin','USA');
INSERT INTO eav ('peach','fluffy','yes');

And then try running queries on it, to play around, learn how it works, etc.

In general though, the above data organization scheme doesn't really mesh well with relational database theory. The above meshes more with document database theory, like Couch DB. I'd look more into that if you need to store/manage data of EAV nature in the wild.

J. Polfer
  • 12,251
  • 10
  • 54
  • 83
  • If you have access to a Siebel system, the LOV system works in the exact same way. – J. Polfer Aug 26 '09 at 18:00
  • Doing that would be easy, but after about 2 seconds I'd be asking a bunch of questions, hence why i was really looking for an existing article or framework that would have already answered those questions. – Jaimal Chohan Aug 26 '09 at 18:02
  • @ Jaimal - What kinds of questions? Your question states that you want an example schema to play around in. – J. Polfer Aug 26 '09 at 18:08
  • 1
    A decent EAV system would have meta-data tables, so playing around with this simple example isn't going to demonstrate much. – codeulike Nov 19 '10 at 13:42
-2

We have a very advanced, mature EAV solution out there on Github - https://github.com/2sic/eav. It's fairly difficult to just install though, as it's primarily used as the backend of a content management system called 2sxc (also on github).

As it's extremely advanced, supporting many data-types, input-types, generic input masks and more I believe you'll find it very inspiring. To try it out, the best way is to install DNN (a .net web platform) and 2sxc (the content management system for DNN). I think you'll be amazed at what is possible.

Note that the entire UI is AngularjS based, so it's a very modern setup. You can also find more blogs/videos on http://2sxc.org.

iJungleBoy
  • 5,325
  • 1
  • 9
  • 21