Hi!

I've recently started using the EAV model for a new application I'm building.

The application is about employee's, software developpers, in a large company.

Every employee has different skills (ASP.NET, C#, HTML, Javascript, etc) and for each skill, he has a level (how skilled he is in that technology)

This picture shows the diagram of the situation, without the levels included.

http://i43.tinypic.com/f28036.png

When i have employee number 1, name being attribute 1 and skill being attribute 2, I can create the following value table to assign a name and several skills to that employee:

http://i40.tinypic.com/2s8sg1v.png

Now here's my question: How can I store the level of knowledge these employee's have for each skill, sticking to the EAV model?!

thanks alot, and please ask me if anything 's unclear!

One question:
Why on earth would you use EAV to model something like this?

In order to model entities and attributes you cannot know at design time there is some (some) justification, but for something known like this it makes no sense.

I suppose your question boils down to "how do you model non-key attributes in EAV where the primary key is a composite key".

First of all thanks for your reply,

I have to use EAV for this application. My 'teacher' ordered me to, so there's no getting out of that.

Ehm Im affraid I dont really understand this translation. I'd just like to find a way to store those levels. Do I add a table between 'value' and 'attribute'? Or another column in table 'Value'?

I dont know..

thank you

Here's my question: why the hell would you want to use EAV for this?

Sniped by a flump!

OK, now my question is: Why the hell is your "teacher" insisting that you use a notorious design anti-pattern?

because this model is very dynamic and allows adding and removing attributes very flexibly.

Now please accept the pattern I'm stuck with.. Is there a way to fix this?

thanks alot

Hmm perhaps this would do the trick?

I create 'skill' entities, that contain a name and a level, and I assign those entities to the skill attribute of 'employee' entities!!

http://i43.tinypic.com/2j2defp.png

what do you think? Am I allowed to do this?

thanks in advance!

No it isn't and no it doesn't. It's a model that makes it incredibly difficult to manage anything. From a data integrity perspective it is generally worse than useless.

If you are "allowed" an EAV model then anything is possible I suppose. You are demonstrating one of the obvious disadvantages however, which may even be the point of the exercise. You presumably know the data type and the business rules that apply to the Skill level. Yet you are using an arbitrarily-typed "bucket" column that also contains all manner of other data. Therefore it is very hard to ensure that the Skill data will be valid or consistent or to control anything else about it.

If the customer is lucky or smart then then they will quickly realise that such a model isn't fit for purpose and will want to replace it. If they are unlucky then it will take them a longer time to realise, after which time the database will contain mountains of garbage and fixing it will be very expensive.

Why is your teacher asking you to model this in EAV? this is a simple thing to implement in a relational design. Your EAV design is not going to be suitable as it only holds attributes against one type of entity yet you need to store skills against people and levels against each persons skill level. The use of id's in your EAV design also only complicates things.

Keep in mind that the assignment that you've been given is probably intended to demonstrate just why you shouldn't do this... The intended lesson is probably why you shouldn't use EAV, not how you should use it.

Using EAV to model well structured employee data with business rules is comparable to using Excell to write a love letter. It can be done. With enough work, you might be able to produce a decent product. You'll probably never be happy with it, but won't be willing to spend the effort to fix it.

The advantage of EAV is that it neither requires nor permits any kind of structure in the data stored. EAV basically creates a "data bucket" which associates names with values, and that's it... There is no way for the database to enforce squat, you must rely utterly on the code to get (and keep) everything correct.

The folks that are trying to help are pitching a fit because they've had to bail too many people out of disasters caused by using EAV. There are a tiny number of cases where EAV is justified and the correct solution for a problem, but I've seen three out of many thousands of problems that were properly addressed by EAV. While it might seem that the folks who have responded are "hounding you", they're really trying to save you from a really, really bad design that you've been "ordered" to use by your teacher.

-PatP

You have only mentioned, in sum total, one attribute: "Skill".

How I wish that were true, but past experience with IT Academia leads me to believe that you are overly optimistic.