Storing multiple values in single SQL row

Question

I'm trying to modelize a configuration table for my application, and I'd like to have your opinion on how to store multiple values for a configuration value.

For example, how to store the available languages? How to store the allowed files extensions, etc.?

I'd like to have a field key and a field value in my table configuration, but I have multiple ways of storing the value:

Serializing a PHP array and storing this in one row,
Storing all values separated by a comma, like this 1,4,9,42
Having multiple rows with the same key but different values. I don't see how I could get this working, it would be very unusable...

Thanks for your help :)

Where will you store the database configuration? Did you consider not storing application config in the DB? — Alin P.
– Alin P., Commented Dec 13, 2014 at 22:54
I meant to store my website configuration in my database, under a table named 'configuration'. Sorry if this was unclear. — AntoineB
– AntoineB, Commented Dec 13, 2014 at 22:56

bitops · Accepted Answer · 2014-12-13 22:59:30Z

4

Leaving aside any discussion of whether or not this is a good idea, many people do this. A very common approach is to have a 'blob' field on a 'configuration' table and store your data in JSON format. You'll serialize and deserialize yourself but that's no big deal.

answered Dec 13, 2014 at 22:59

bitops

4,3604 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paweł Tomkiel · Accepted Answer · 2015-03-18 19:18:38Z

If you want to store your configuration in database (it's not recommended, but it makes sense if you have many instances of your application) - let's analyze it:

Approach with storing multiple rows with same key - let's forget about it ;)
Storing many values separated by , - that one would make some problems if you'll need to store value with coma (for example: 10,000 as ten thousand). Then you'll need to modify it to store values like in CSV file encapsulated with ".
Best idea - storing serialized PHP values. As your app is written in PHP that would be the best native method. Using this approach would be as easy as:

$configValue=unserialize(getMyValueFromDb("myKey")); //or saving it after change $configValue="new value"; saveMyValueToDb(serialize($configValue));

Also - PHP serialize and unserialize take care about escaping values and convert it properly so you don't need to worry about some coma-separated-string wrongly split with explode.

Collectives™ on Stack Overflow

Storing multiple values in single SQL row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related