Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2010
    Posts
    4

    Entity-Attribute-Value model

    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!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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".

  3. #3
    Join Date
    Apr 2010
    Posts
    4
    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.

    I suppose your question boils down to "how do you model non-key attributes in EAV where the primary key is a composite key".
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Here's my question: why the hell would you want to use EAV for this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Sniped by a flump!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    OK, now my question is: Why the hell is your "teacher" insisting that you use a notorious design anti-pattern?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Apr 2010
    Posts
    4
    Quote Originally Posted by blindman View Post
    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

  8. #8
    Join Date
    Apr 2010
    Posts
    4
    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!

  9. #9
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by xhcsurge View Post
    this model is very dynamic and allows adding and removing attributes very flexibly.
    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.

    Quote Originally Posted by xhcsurge View Post
    what do you think? Am I allowed to do this?
    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.

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by xhcsurge View Post
    and please ask me if anything 's unclear!
    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.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by xhcsurge View Post
    because this model is very dynamic and allows adding and removing attributes very flexibly.
    You have only mentioned, in sum total, one attribute: "Skill".

    Quote Originally Posted by Pat Phelan View Post
    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.
    How I wish that were true, but past experience with IT Academia leads me to believe that you are overly optimistic.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •