Best Practice for Storing Sets of Options

PHP
1

Hello,

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?

Thanks.

2

Is this an actual requirement, or is it just something you think might be nice to have?

3

No, It’s just something that users might like. Otherwise customization of the application can be done using the mentioned files too.

4

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.

5

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

:slight_smile:

6

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.

7

if you’re using numeric keys, yes, but you don’t actually have to do it that way

8

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.

9

that’s a pretty good reason right there to choose your keys wisely

i’m curious why you think string form is not wise

in any case, however, this complication ~really~ nails the coffin shut on storing user options is flat files

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

Mac

11

if you really like the idea of using files, go right ahead

:slight_smile:

12

I figured the settings were global for the application. If not, I concur with you.

13

Can you explain what your optimal solution would be in this case?

14

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

:slight_smile:

15

Sure:

  1. store options in a flat file/php script, store actual value in db column
  2. store options in separate table, store numeric id of option in db column
  3. store options in separate table, store string id of option in db column
  4. store options and value in db ENUM column (might be more difficult to do different languages)

Did I miss any?

16

i was sort of hoping for an actual table design

17

Alright, I just threw these together and I’m not sure it’s what you’re looking for:

String ID:

CREATE TABLE `cars` (
`name` VARCHAR( 255 ) NOT NULL ,
`color` VARCHAR( 255 ) NOT NULL ,
) ENGINE = INNODB;

CREATE TABLE  `test`.`colors` (
`color` VARCHAR( 255 ) NOT NULL ,
`language` VARCHAR( 5 ) NOT NULL
) ENGINE = INNODB;

Numeric ID

CREATE TABLE `cars` (
`name` VARCHAR( 255 ) NOT NULL ,
`color_id` INT NOT NULL
) ENGINE = INNODB;

CREATE TABLE  `test`.`colors` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`color` VARCHAR( 255 ) NOT NULL ,
`language` VARCHAR( 5 ) NOT NULL
) ENGINE = INNODB;

Enum:

CREATE TABLE `cars` (
`name` VARCHAR( 255 ) NOT NULL ,
`color` ENUM('red','blue','green','rojo','azul','verde') NOT NULL ,
) ENGINE = INNODB;
18

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

does that explain it well enough?

19

Sorry about that, I didn’t realize we had a fixed domain, I thought we were just talking about options in general.

Yes it does, thanks!

20

Use database tables to hold the options, cache them in APC/XCache/whatever. Guessing only a handful of them, should be trivial to maintain.

Eliminate the joins.