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

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

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!

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.

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.

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.

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...

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...

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.

Muke, unless I am misunderstanding Pat, he is in favor of EAV for this.

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.

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.

Mike, have you ever used a db to store app vars instead of in a file?

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

of course it would

Every application I've ever done. The database connection variables (server, database name, user name, password) obviously can't be done this way .

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.