I have always stored sets of options (like marital status, sex, hair color, level of study, country etc.) in database tables. I would then save an index to the selected option in user’s profile table.
Pros:
Admin of the website can easily customize options using a web based interface.
Cons:
I am sometimes forced to join up to 10 tables (in MySQL) to add those information and produce information pages.
1- What other Cons or Pros you see for above method?
2- How about storing these options in simple PHP arrays and keeping them in a file e.g. “constants.php” and using array indexes in profile tables etc.
Which approach is better in your opinion? Keeping options in file or database?
In that case, I would suggest that you code it into files, rather than try to manage it in the database. It’s complicating things a lot, and if you later find that it’s absolutely needed, you can always add it in later. Chances are that it’s going to be you (or at least a programmer) who’ll be changing such settings anyway, and then it would actually be easier to have it in a file, than in the database.
troels, you’re not suggesting storing a file for each user are you?
may i respectfully suggest that this data be stored in a database table instead
sarmadys, if you’re finding yourself writing join queries or 10 tables or more just to pull out user options, perhaps your tables aren’t designed optimally
No, we are talking about option sets (i.e. education level: high school, bachelor degree, …).
If I have 5-6 options (sex, education level, country, state, hair color …) and option values are stored in databases I’ll need to join the option set tables to get build the whole view.
Yes, I am using numeric keys. What other options I have?
I guess storing actual values in the table (in string form) is not wise. The application is a multi-language social network and values need to be changed based on the selected language.
By selecting each of the languages for example the word “single” needs to be changed to “ledig”. Now with “option set tables” I can bring out the values (in all languages) using the key saved in user profile table and narrow down using language code.
With files I use the option key from the user profile table and also the language code to find the actual value in the PHP array.
Why is that? I have multidimensional array. One dimension is the index of each option value and the other dimension is the language index.
By the way, thank you very much for your time. I really appreciate your help.
sure, but i think it might make more sense if we could get the original poster, or yourself if you’re up to it, to post a table design for the options, including the ability to store synonyms in different languages, and then i will suggest a subtle alteration
i’m not comfortable venturing off into the mythical realm of car colours when we are supposed to be dealing with user options, but let’s give it a try…
in particular i wanted to see the key declaration that includes the language option
to go along with your example, the table should look like this –
CREATE TABLE colors
( color VARCHAR( 255 ) NOT NULL
, language VARCHAR( 5 ) NOT NULL
, PRIMARY KEY ( color, language )
) ENGINE = INNODB;
let’s leave aside the whole business of character sets (some languages will require utf-8, n’est-pas?)
now obviously the cars table will need a foreign key to this colors table, and of course the foreign key will consist of two columns, right?
so therefore when displaying the cars, there is no need to join to the colors table