Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can someone explain what is the difference between a Configuration table and a Data table in a database? And how to design or implement Configuration table? I'm using MySQL and the requirement is to build a Student Database. I understand that the data table will be having fields related to the student and I've come up with a design for that. The operations that I will be implementing are

  • Adding
  • Displaying
  • Deleting
  • Sorting
  • Searching

How do i go about designing a Configuration table for such a requirement that is dynamic, so that further options can be later added in the future?

Any kind of help is appreciated. Please let me know the reason before downvoting. Thanks in advance.

share|improve this question

2 Answers 2

I have proposed a fairly robust solution to this issue on Stack Overflow (see here).

I won't reproduce the whole answer here (see SO for the details), but the basic design is as follows:

This is a Property Bag approach for configuration settings; see the following ERD:

Property Bag ERD

This design allows you to table-drive the rules around what your settings are. This is convenient, flexible and easy to maintain, while avoiding a free-for-all.

Using this design, you can table drive the allowable settings including enough metadata to enforce some rudimentary constraints/sanity checks on the values selected (or entered) by users.

If you don't want to go to this extent, then a simple EAV (key/value pair) table would give you access to settings while allowing new settings to be created in the future without requiring a schema change.


EDIT: (from the quoted SO answer: A few more details, including some examples...)

Note that the ERD, above, has been augmented with more column details (range values on SETTING and columns on ALLOWED_SETTING_VALUE).

Here are some sample records for illustration.

SETTING:
+----+------------------+-------------+--------------+-----------+-----------+
| id | description      | constrained | data_type    | min_value | max_value |
+----+------------------+-------------+--------------+-----------+-----------+
| 10 | Favourite Colour | true        | alphanumeric | {null}    | {null}    |
| 11 | Item Max Limit   | false       | integer      | 0         | 9001      |
| 12 | Item Min Limit   | false       | integer      | 0         | 9000      |
+----+------------------+-------------+--------------+-----------+-----------+

ALLOWED_SETTING_VALUE:
+-----+------------+--------------+-----------+
| id  | setting_id | item_value   | caption   |
+-----+------------+--------------+-----------+
| 123 | 10         | #0000FF      | Blue      |
| 124 | 10         | #FFFF00      | Yellow    |
| 125 | 10         | #FF00FF      | Pink      |
+-----+------------+--------------+-----------+

USER_SETTING:
+------+---------+------------+--------------------------+---------------------+
| id   | user_id | setting_id | allowed_setting_value_id | unconstrained_value |
+------+---------+------------+--------------------------+---------------------+
| 5678 | 234     | 10         | 124                      | {null}              |
| 7890 | 234     | 11         | {null}                   | 100                 |
| 8901 | 234     | 12         | {null}                   | 1                   |
+------+---------+------------+--------------------------+---------------------+

From these tables, we can see that some of the user settings which can be determined are Favourite Colour, Item Max Limit and Item Min Limit. Favourite Colour is a pick list of alphanumerics. Item min and max limits are numerics with allowable range values set. The SETTING.constrained column determines whether users are picking from the related ALLOWED_SETTING_VALUEs or whether they need to enter a USER_SETTING.unconstrained_value. The GUI that allows users to work with their settings needs to understand which option to offer and how to enforce both the SETTING.data_type and the min_value and max_value limits, if they exist.

share|improve this answer
    
So, I should have a table with all the options that I want to give the user? i.e., a table with columns such as Adding, Deleting and so on? And how would i be able to dynamically add another option later on? –  Anjan Baradwaj Sep 27 '13 at 5:20
    
@AnjanBaradwaj - The idea is not to use columns, but to use rows. This is how EAV works. Follow this link to the answer on StackOverflow: stackoverflow.com/questions/10204902/… and look at the sample data to understand how this model works. –  Joel Brown Sep 27 '13 at 11:58
    
I'm still a newbie, so I'm not getting it properly. I was asked to come up with a Configuration table for a student database and I was told that it should contain all the options that the user should be given. And also, it should be flexible enough to add features late on without having to change the back-end code. Can you walk me through what exactly it means and how to go about it? –  Anjan Baradwaj Sep 27 '13 at 12:10
    
@AnjanBaradwaj - See the edit to the answer. I've copied more of the answer from the SO question. This shows examples of how to use the model to create two different kinds of configuration settings using table-driven rules which won't require any coding changes to your back-end code to be able to read. –  Joel Brown Sep 27 '13 at 12:17
    
Sorry, but I'm not going any forward with that. Finding it complex. Any tutorial or something? –  Anjan Baradwaj Sep 27 '13 at 12:24

It's not clear what you're going to be configuring... Usually a configuration table just stores key/value pairs that are used by the application at a global scope. An example might look like this:

config
------
config_name  | config_val
--------------------------
foo_timeout  | 50
maxRetries   | 8
BGRZEndpoint | www.something.com/BGRZService
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.