Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: EAV and Me.

  1. #1
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803

    Cool EAV and Me.

    I am wondering if it would be acceptable to us EAV for a single table that would store system variables for an application. Only attributes and their values would be stored in this table with the PK being the attribute column.

    I'm currently using these attributes/values in an array in a file. I'd like to put them into the db without changing the structure of the application code. EAV would give that to me. Would I be asking for a big problem if I did this?

    Thanks!
    Frank

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Keeping in mind that EAV is usually a problem eagerly in search of its next victim, this could be a good way to use it. I've done this in the past but be careful, once you unleash the Djin, it can take over your life if you aren't very careful.

    -PatP

  3. #3
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    It is tempting...

    I understand about the Djin taking over because my mind started to wander, then thought about how convenient it would be to store all of the application variables as well.

    Heeding your advice, I think I'll stop at just storing the system vars and count myself lucky.

    Thanks Pat!


  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I am wondering if it would be acceptable to us EAV for a single table that would store system variables for an application.
    This seems (to me) a fine way of storing a group of system variables in the database. Obviously the alternative 3NF approach is to have a table consisting of one record but with lots and lots of columns but I don't see this as a better solution. This 3NF table would not connect to the other tables in your database and wouldn't have a key so I don't see this as an improvement.

    To add a variable using this 3NF approach means you'd need to alter the database DDL each time you want to add a new variable, plus test all the code that uses this table (we'll assume all the code in the system needs testing as all the code would access the system variables).

    To add a new system variable using the list of params approach is just a question of adding a new record. No need to alter the DDL, no need to test existing code. I only ask as this is an approach I use a lot for this type of data and it has worked well over time (in my opinion). Normally you'd have a function to both add or retrieve a system variable and there would have to be restrictions on who can add new variables or alter the value of existing variables.

    Perhaps someone could explain exactly why it would be so bad to store the system variables this way and what method I should be using?

    Mike

    PS I know it's unwise of me to get into these debates but the advice given just seems wrong to me.

  5. #5
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    This seems like an acceptable use of EAV, at least to me. Having a table with a single row of vars isn't a good idea either. By the way, I don't think that table would qualify as even 1NF, let alone 3NF IMHO.

    The last option I see would be to use a normalized table structure using more than one table. It would mean too many mods to the app code and I don't want to get into that.

    Pat didn't elaborate on why, but his answer was good enough for me. Honestly though, I am tempted to store all of my variables in a single EAV table; if nothing else, to see how it would work. It might be kinda cool.

    I have seen many database structures that use a single row for vars but never EAV.

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm interested in why Pat (or any of the experts) would not use this approach in this particular scenario. I'd also be interested in what they see as a better solution.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I'd use it in this scenario - it's effectively a config file in a database table...
    As you mentioned Mike, it doesn't have any relational dependencies...
    George
    Home | Blog

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    I think the other spin of the coin is that these are application rules and should be stored in the application tier rather than the data tier...
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Yeah George I think I agree. In theory, it sounds great to store the app vars but in the end it may be more hassle than its worth. The system vars however are a different story. I like that idea.
    Last edited by Frunkie; 12-12-08 at 07:33.

  10. #10
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Quote Originally Posted by mike_bike_kite
    I'm interested in why Pat (or any of the experts) would not use this approach in this particular scenario. I'd also be interested in what they see as a better solution.
    Muke, unless I am misunderstanding Pat, he is in favor of EAV for this.
    Quote Originally Posted by Pat
    this could be a good way to use it

  11. #11
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by georgev
    I think the other spin of the coin is that these are application rules and should be stored in the application tier rather than the data tier...
    Certainly many applications keep their system variables in a file rather than in a database. There can be many reasons for doing this (ie the application might not require a database!) but I personally dislike doing this as it means changing a production file to change a variable - in the banking world this would mean a code release with full testing and usually coming in at a weekend to implement it. If all I was doing was changing the email address of the application owner then I'd feel a bit pissed.

    Storing these values in a simple param value table means I can easily have a very simple screen that can change these values. It just makes my life easier and the TCO lower.

    Quote Originally Posted by fjm1967
    Muke, unless I am misunderstanding Pat, he is in favor of EAV for this.
    Perhaps I misunderstood the tone of Pats response - I got the feeling he thought it was a bad direction to go in. I just wondered if he (or any of the experts) felt there was a better way to store such information.

  12. #12
    Join Date
    Mar 2007
    Location
    010101010110100
    Posts
    803
    Mike, have you ever used a db to store app vars instead of in a file?

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by mike_bike_kite
    I personally dislike doing this as it means changing a production file to change a variable - in the banking world this would mean a code release with full testing and usually coming in at a weekend to implement it. If all I was doing was changing the email address of the application owner then I'd feel a bit pissed.

    Storing these values in a simple param value table means I can easily have a very simple screen that can change these values. It just makes my life easier and the TCO lower.
    So storing these config variables in a database effectively bypasses release management... which is there for a reason

    I wouldn't have to raise a release to change something as noddy as the appllication owners email address in the config file... If I was changing the default VAT rate value, then I think they could get pissy if I just did a sneaky, un-released database update

    Sadly, I don't see this as enough of a justification to avoid config files. I'm still for the idea, just not for the same reasons
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by fjm1967
    By the way, I don't think that table would qualify as even 1NF, let alone 3NF IMHO.
    of course it would
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by fjm1967
    Mike, have you ever used a db to store app vars instead of in a file?
    Every application I've ever done. The database connection variables (server, database name, user name, password) obviously can't be done this way .

    Quote Originally Posted by georgev
    So storing these config variables in a database effectively bypasses release management... which is there for a reason
    Sadly release management in banks doesn't have grey areas, if it's a production file then it needs to go through a release procedure. I agree this isn't an argument for EAV but an argument against bank release procedures. But even without banking release procedures I'd still put my system vars in a param value table.

Posting Permissions

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