best way to store data in db

[eluser]no one[/eluser]
hello all,
i have a large site with large configration data like(name,logo,email,status,....) this may be reach 100 configration variables.
now iam asking what is the best way can i tore these variables in db, i have two solution and dont know what is the best :-
1- i will create one coulmn foreach variable so in this case my configration table will contain one row with 50-100 field. this way will be easy for updating these variable (on query will update all column).
my table structure will be like his
name status email
foo 1 [email protected]

2- i will create one row foreach variable like this

key value


name | foo
status | 1
email | [email protected]

and so on,

but in this case when i want to change site configration i will make 50-100 sql-query to update each row .


which one of these two ways is the best,or is there another way?!

[eluser]jedd[/eluser]
[quote author="allamovech" date="1250779020"]
... in this case my configration table will contain one row with 50-100 field. [/quote]

{shudder}

This would be quite the mess.

The fact that you say '50-100' kind of suggests you haven't really worked out all the attributes (and, I suspect entities) that you need to track yet.

2- i will create one row foreach variable like this

key value

name | foo
status | 1
email | [email protected]

I'm curious to see how you'd identify the multiple rows grouped back to a particular individual?

which one of these two ways is the best,or is there another way?!

Option C.

I would suggest you do some research on normalisation. Start with the wikipedia article on it, and then hunt down some tutorials (The Google is your friend here) and maybe, given this sounds like a non-trivially sized project, invest in some books on the subject of DB design.

[eluser]no one[/eluser]
thanks for your advices,
please read this article and you will understand what i mean
http://bakery.cakephp.org/articles/view/...-in-the-db

[eluser]jedd[/eluser]
Oooh .. let's go back a bit.

The fact that you're recording things like name, status and email address - I assumed you were talking about user related data. Now I'm not so sure - so, are you talking about recording these data per user or just once for the whole site?

[eluser]renownedmedia[/eluser]
Maybe have a table with the following columns:

Code:

user_data:
id, user_id, type, value (VARCHAR255)

And a second table:

Code:

user_data_types:
id, name

And if you want a users data, do something like this:

Code:

SELECT value FROM user_data WHERE user_id = 27 AND type = (SELECT id FROM user_data_types WHERE name = 'email' LIMIT 1) LIMIT 1

Not sure if that is the ideal version of normalization... But it should do what you want.

[eluser]no one[/eluser]
[quote author="jedd" date="1250793105"]Oooh .. let's go back a bit.

The fact that you're recording things like name, status and email address - I assumed you were talking about user related data. Now I'm not so sure - so, are you talking about recording these data per user or just once for the whole site?[/quote]

ok , now you understand what i mean.
i talking about recording config values for the whole site.

[eluser]jedd[/eluser]
[quote author="allamovech" date="1251032956"]
i talking about recording config values for the whole site.[/quote]

That's easy then - option (2).

Have a row for each configuration option, probably with [id, item, description, value]

Note that you'll have a decision to make on whether you store all values as strings and mung them back to the right thing on the way out in PHP .. alternatively you could have separate configuration tables depending on the type of config (int, string, bool). Either approach has a mess-factor.

[eluser]no one[/eluser]
[quote author="jedd" date="1251039450"]
That's easy then - option (2).[/quote]
ok but in this option , when i want to update my config value i will make one query for updating each value , assume that admin site want to change these value , he will write

mysql_query("update setting set value='$new_name' where key='name'");
mysql_query("update setting set value='$new_email' where key='email'");
mysql_query("update setting set value_status='$new_name' where key='status'");
mysql_query("update setting set value='$new_name' where key='logo'");
and so on....

so there is will be a lot of queries in this option.


but in the second option it will be one query for updating all values like this :-
mysql_query("update setting set `name`='$new_name',`email`='$new_email',`site_status`='$new_status',`logo`='$logo'");

but in this option (second option) if these values are 50 , my table will be 50 column. is it (increasing table column) a bad method ?

[eluser]jedd[/eluser]
I think perhaps you are confused about terminology.

If you have a record for each setting, you'll have 50 rows, and that number might grow.

Your table will have a fixed number of columns - probably 3, maybe one or two more, but it won't grow dynamically.

Another option you didn't mention, but given you are unclear about your design it might be worth considering - have all your settings in a big php array, that you serialise before inserting into the database. This works really well if you're happy to pull out ALL the configuration data whenever you need ANY of the configuration data. In reality this is a relatively modest cost, but it depends on how your system is structured. If every user that hits the system pulls this data out, though, this might not be the best performance option. Maybe a hybrid approach, but the added complexity there probably isn't worth the bother (either).

Given you'll be updating the system settings infrequently, I wouldn't be too worried about looping through dozens of iterations of updates (though there are of course ways to reduce that load) given you'll be doing it rarely. It means you can pull out whichever single config item you need, though, and that's why I'd probably head down that path.

Aside: Do you really have 50+ configuration items for the whole system that you need to be configured via the web interface?