13

I want ot create database table in which I can store application settings. Which of the two table designs is better:

  1. Store the settings in a column and one row for key - something like this:

enter image description here

  1. Store the settings in only two rows - something like this:

enter image description here

Which of the two is more appropriate for storing application settings?

Best wishes

Peter Penzov
  • 1,126
  • 134
  • 430
  • 808
  • Is strong typing important to you? In the 2nd option, all you values will have to be stored as varchars. – Asaph Mar 10 '12 at 14:25
  • Duplicate question - http://stackoverflow.com/questions/1387294/best-table-design-for-application-configuration-or-application-option-settings – Chris Gessler Mar 10 '12 at 14:34

4 Answers4

6

Use the second method. Obviously it is far more scalable. You will never need to add additional columns when you need to add more options.

I would only advocate the first method if the options are very limited and fixed, and all have different data types. That's an area where the two differ considerably - if you have a big mix of numeric and character columns, you have really no choice with the second option but to store them all as VARCHAR. However, for a settings table that will have a very limited number of rows and won't be subject to a lot of INSERT and UPDATE, that probably isn't a big issue.

You would not want to use the second method for a regular table (not storing mostly static application settings) that needs to be highly accessible, or used for calculations for example, where you would constantly need to be type casting values to manipulate them.

For static data infrequently accessed or modified, the second method works well though.

Michael Berkowski
  • 267,341
  • 46
  • 444
  • 390
  • Yes I will store numbers and strings into the table. I suppose that storing numbers in varchar2 is not a problem. – Peter Penzov Mar 10 '12 at 14:29
  • @user1103606 Not it isn't a problem unless you always have to cast them back to real numeric types in order to do arithmetic. – Michael Berkowski Mar 10 '12 at 14:31
  • Would you recommend a third column that would define the underlying data type that would be behind the `VARCHAR` value? If you had datetime, numeric, and character settings it seems like you would want to know what datatype to cast them to. – swandog Jun 20 '16 at 16:14
  • @swandog I don't think it would hurt, but since application code would have to parse and handle the settings, I would expect it to know how to validate and appropriately cast whatever values it retrieves in context and bail if the type isn't valid. Assuming the application code is DB client code, as opposed to something in the RDBMS like a stored procedure. Would be tough to cast in the RDBMS. At least, since these are settings prob queried as key/value pairs, you don't really lose type-specific optim like date functions, string manip, GROUP/ORDER indexing etc. – Michael Berkowski Jun 20 '16 at 16:26
  • hey, ho do you query this kind of setting? do you guys keep querying everytime you need it? – Robert Limanto Mar 12 '17 at 16:40
  • @RobertLimanto I would probably avoid querying it every time it's needed, and instead find a way to cache it in memory after first access. It really depends on the application code as to the best way to do that. – Michael Berkowski Mar 12 '17 at 17:20
  • @MichaelBerkowski can i know the good ttl for small application? – Robert Limanto Mar 13 '17 at 09:50
  • @RobertLimanto For application configuration values, they are not likely to change often so you could use a very long ttl. For something like PHP where the application execution is a brief event, I would load all the configuration from the db into an object that the code references. If you have access to some other time-managed cache that is faster to access than your database and choose to cache values there, the ttl can be very long because configuration settings do not often change. – Michael Berkowski Mar 13 '17 at 11:30
1

It really depends on how often these values change and how varying there types are:

If for example you have a finite number of settings of many varied types, then the horizontal layout is better, because you can specify each type. But everytime you want to add a new one you'll h ave to alter the table.

Conversely if you have many different types and people have many different arrangements of the settings, and perhaps even you know that new ones will continue to come in the future, then the second listing will be better. The trouble though is that you'll be stuck with varchar(nnn) type for each value, so the db won't be able to help you with the typing very much.

Nathan Feger
  • 19,122
  • 11
  • 62
  • 71
0

Option 1 is "better" until there are a lot of settings (ie: dozens, hundreds). If there are "a lot of settings" there may be better ways to model them.

As one example, maybe that information doesn't even belong in this system and could be pushed to its own independent system that manages that kind of data for all applications.

As another example, maybe some of those settings actually could relate to something else in your system, maybe even a concept that you haven't yet introduced; IE: maybe that data varies by Season, or by Company, and you haven't thought to model those types yet.

I would save Option 2 for cases where it's truly necessary (ie: you have thousands of values to write). Yes, it's very flexible, but it also loses a lot of semantic meaning, and is overall a failure of your data model. (It's actually a degeneracy of the use of the database and/or ORM and should only be chosen very carefully.)

Dave Cousineau
  • 12,154
  • 8
  • 64
  • 80
0

I prefer the 2 column method. The draw back to the a new column for each configuration is that you need to change code to add new settings.

Arqam Rafay
  • 97
  • 6