Storing site settings on the server

[eluser]jedd[/eluser]
[quote author="techneke" date="1253570064"][quote author="jedd" date="1253569686"][quote author="techneke" date="1253560970"]
I don't want to add a column to the database for each setting, that seems silly.
[/quote]

Please explain this belief.[/quote]

I don't ever need to search or sort by these settings, so why would I want to manage them with columns?
[/quote]

Hey, you can't just invert the question and ask it back at me. I already know why I'd use columns - I'm asking you why it'd be silly!

If I use a "settings" column with a a string in it, I can manage all of the settings, change the number of settings, the setting values, and the type of data stored in those settings with just a a flick of PHP.

Do you expect to change the settings you manage regularly and significantly, once you are running the site live?

A 'flick of sql' can change a table definition reasonably quickly, too. If you're only familiar with GUI front-ends to databases this may not be something you're comfortable with, I concede.

Having to go into something like phpMyAdmin and add a column for one thing seems asinine. I could end up with 10,000 columns. What if I deprecate a setting, go back in a delete a column?

Hmm .. let's work through those.

Are you coming up with new ideas on your settings all the time, such that you frequently have to add setting options? If so, you need to stop acting, and start thinking - and once you've got your design nutted out a bit better, return to your IDE.

While I'm sure 10,000 is a ludicrous example you've thrown out there just for effect, consider what happens when you have try to unserialise a string long enough to contain a 10,000-wide PHP array.

My money, in that race, is on MySQL. Of course, you don't have 10,000 column tables in the first place, and in reality you're probably looking at somewhere around 20 user-specific (as opposed to user-something hybrid) settings.

In any case, the 10,000 is clearly silly, so let's stick with your version of reality.

How many settings do you actually have right now that are attributes of a user (and only a user)?

If you deprecate a setting, you have the same problem in both serialised-array and separate-column approaches. I gather from your first observation that, using serialised-arrays, if you chose to deprecate a setting, you would not bother writing a function to search through everyone's serialised settings and remove that one item from the array, and re-write the serialised array into the database. Is that right?

I think it's actually easier, in that instance, to just alter the table (one command).

Your code, of course, has to recognise what each setting does, and what to do with that setting, no matter which approach you use to store the user's setting data. This is an important thing to remember, and I suspect a lot of people overlook this when they see a 'generic storage' method for their settings. You can always provide a 'generic' front end to whatever storage engine you use.

It's not very modular, no does it seem like it's best practice of any kind.

Can you please explain what you mean by 'modular' in this context.

what if I have 10 different emails that go out?

wants_mail_1 wants_mail_2 want_mail_3 wants_mail_4 ... wants_mail_10 all columns? Ugh.

[/quote]

A common source of confusion, and something you'd handle outside of this context.

As BrianDHall intimates, and as 'best practice' types tend to advocate, you'd have a cross referencing table to keep track of multiple attributes of the same type.

Out of curiosity, how do you handle it now - do you insert those data into a second dimension - an array within your settings array - or do you have a unique key for each of those (wants_mail_x) items.

[eluser]Myles Wakeham[/eluser]
I'm with Jedd on this one. Putting persistent data of any kind in a database is just plain common sense. Trying to avoid a database without good reason to, and dealing with persistent data, might work fine for an INI file in a Windows GUI app with a single user, but doesn't work in a distributed web application environment.

And what about this idea... What happens when your killer app is so popular you have to load balance it across multiple servers? Database might be a good idea, IMHO.

Now if you are thinking about 10,000 columns, you haven't modeled your data at all. You need to consider a one to many table relationship to handle the data if you have that number of columns.

Myles

[eluser]Zack Kitzmiller[/eluser]

Do you expect to change the settings you manage regularly and significantly, once you are running the site live?

Yes. It's not a personal project I work on, I answer to a board of directors.

A 'flick of sql' can change a table definition reasonably quickly, too. If you're only familiar with GUI front-ends to databases this may not be something you're comfortable with, I concede.

I guess that's true, It just seems so 'static' for lack of a better term. I don't really want to hard code column names into my PHP. I'd rather just loop through a string, foreach setting, apply it as necessary.

Are you coming up with new ideas on your settings all the time, such that you frequently have to add setting options? If so, you need to stop acting, and start thinking - and once you've got your design nutted out a bit better, return to your IDE.

I'm not, the people I work for do. And as much as I'd like to educate them on how "That's not how things work, make up your minds, etc... I need the paycheck, and this is teh real world.. so for now, i'll take it up th... well.. I'll just deal with it.

My money, in that race, is on MySQL. Of course, you don't have 10,000 column tables in the first place, and in reality you're probably looking at somewhere around 20 user-specific (as opposed to user-something hybrid) settings.

Fair enough, but what if I don't know what the column is named, or don't want to? Maybe I only want a setting to apply if it exists.

How many settings do you actually have right now that are attributes of a user (and only a user)?

20 is probably a good estimate. But you never know what is going to be thrown at me..

If you deprecate a setting, you have the same problem in both serialised-array and separate-column approaches. I gather from your first observation that, using serialised-arrays, if you chose to deprecate a setting, you would not bother writing a function to search through everyone's serialised settings and remove that one item from the array, and re-write the serialised array into the database. Is that right?

It would probably be removed at the next settings update, if it was no longer needed. I wouldn't keep stale data one way or another. So, I concede a win for you..

Can you please explain what you mean by 'modular' in this context.

Dynamic, or adaptable.

As BrianDHall intimates, and as 'best practice' types tend to advocate, you'd have a cross referencing table to keep track of multiple attributes of the same type.

Out of curiosity, how do you handle it now - do you insert those data into a second dimension - an array within your settings array - or do you have a unique key for each of those (wants_mail_x) items.

Right now, I have a column for each setting. I just think it's ugly and very.. Microsofty. It works, but it's not elegant or fluid.

Most of the settings are display related. This message get's displayed, this user doesn't want a menubar here, blah blah, etc. etc. The reason I wonder if XML or JSON would be better is because like I mentioned before, this is information that never needs to be sorted or searched. And from what I remember in SQL classes I took back at college, they recommended consolidating as much information as possible if it didn't need to be sorted.

Maybe you guys are right, it just feels lazy to me. (and FWIW, this project isn't build on CI, it's a general programming question).

[eluser]jedd[/eluser]
May I draw your attention to the [url="http://ellislab.com/codeigniter/user-guide/database/fields.html"]meta-database functions[/url], and the idea of standardising your column names - for example 'pref_' for anything that's a preference, and perhaps 'is_' for any system-assigned rights. Sometimes blurry, but that's part of the Fun.


Consider:

Code:

CREATE TABLE member (                           # the USER table, by any other name.
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    handle              CHAR(50) UNIQUE,
    email               CHAR(70),               # email address
    ...
    pref_email_private  BOOLEAN DEFAULT TRUE,   # PREF - Notify by email on private message
    pref_email_public   BOOLEAN DEFAULT FALSE,  # PREF - Notify by email on forum/public message
    ...

And in your model somewhere:

Code:

$fields = $this->db->list_fields('member');
$prefs = array();
foreach ($fields as $field)
    $prefs[]['name'] = substr ($field, 5);

Populating the latter is left as an exercise for the reader. As mentioned previously, you still need code to extract meaning (and apply intent) from whatever preferences you have created, no matter whether you store that pref somewhere in an array or a column

[eluser]Zack Kitzmiller[/eluser]
Yeah.. I do that for the columns that need to be sortable and searchable. It works. I just feel like it could be done better.

Like I said before, maybe it can't, I just don't like accepting things as what they are, and always see room for improvement.

[eluser]jedd[/eluser]
I think my wrap-up comments are pretty easy, then.

I think your attitude, as expressed in that last message, is quite laudable.

OTOH, I think if you are attempting to resolve a management / project-management problem with a technical solution (ie. you are catering to your board's inability to make a decision and stick to it, and their unwillingness to accept that changes have costs) then you, and as a consequence them, will end up being deeply disappointed in the final codebase.


Oh, and the setting-per-column approach is not really Microsofty, IMO. A Microsofty approach would be a completely separate database, that has no decent API, is prone to corruption, can't be purged other than by re-installing the whole box from scratch, keeps on growing in such a way as to slow down everything else that runs on that same computer, and every time a new user decides they want to login, you end up with 14,000 new elements in that database.

[eluser]Myles Wakeham[/eluser]
[quote author="jedd" date="1253591064"]I think my wrap-up comments are pretty easy, then.

I think your attitude, as expressed in that last message, is quite laudable.

OTOH, I think if you are attempting to resolve a management / project-management problem with a technical solution (ie. you are catering to your board's inability to make a decision and stick to it, and their unwillingness to accept that changes have costs) then you, and as a consequence them, will end up being deeply disappointed in the final codebase.[/quote]

I have to agree here. If you want to do the right thing by your company, you need to assume the position of the 'expert' and direct the board (ie. non-experts) towards the most suitable solution to their business problems. Creating code that is going to be a subject of comedy later on by your next generation of replacement isn't helping anyone. And there is something to be said for 'evolution over revolution' here. You are free to innovate as much as you want. But somethings were designed and developed for a reason. Respecting the past to the point of studying 'why' rather than just throwing out any past precedent without proper analysis is just insane. Otherwise you may as well invent your own silicon chip and start building your own concept of a computer to program this whole thing on.

Oh, and the setting-per-column approach is not really Microsofty, IMO. A Microsofty approach would be a completely separate database, that has no decent API, is prone to corruption, can't be purged other than by re-installing the whole box from scratch, keeps on growing in such a way as to slow down everything else that runs on that same computer, and every time a new user decides they want to login, you end up with 14,000 new elements in that database.

And costs thousands per year, is subjected to vendor lock-in, and then starts trying to tell the world that they invented open source.

Myles

[eluser]BrianDHall[/eluser]
...and then sometimes it just doesn't work.

[eluser]Phil Sturgeon[/eluser]
techneke: These guys have a good point. Settings-per-column works fine and in my opinion is not a bad way to go.

However I would be torn between that and a join table for PyroCMS as I would want to have the ability to add settings to the database on a MySQL user with minimum permissions (i.e. the user can INSERT, SELECT, UPDATE, DELETE but not ALTER tables).

My last suggestion was slightly off and would not have worked, it would need to be a join table instead.

settings
--------

id | slug | title | default | value | etc... as I currently have

user_settings
-------------

user_id | setting_id | value

Make the primary key on user_id, setting_id (we call that a composite key) and you are good to go.

As I said its up to you which way you go with it, they are both pretty much the same but their suggestion will scale better as the link table wont grow as much and there wont be as much joining.

[eluser]Damien K.[/eluser]
Too interesting to not respond! I usually don't even both with a response for topics that has at least 1 reply.

It is unfair to bash Microsoft like this. Their .Net framework is, in my opinion, the state-of-the-art in web development, particularly for enterprise applications. It can be considered a generation ahead of Java, and PHP (along with other popular interpreted languages) cannot even compete until more recently with the introduction of "Web 2.0". Even with Web 2.0, there are still a lot of missing pieces... Furthermore, there's no IDE better than Visual Studios. I know this is rather off topic and I'm going to get flamed, so I plan to hide under my bunker while you all attack this post -- but I'm not going to respond so don't bother!