3

So, I got a table UserProfiles in my database, that, similar to C#'s Settings file, holds a property with the appropriate datatype and the specified setting in the profile.

Now each time I add a new setting, I need to update the database, the model and end up rewriting the queries.

I've then thought about changing the table to only contain two columns: Key and Value with a varchar datatype to store the users settings... But what is considered to be best practice in such design-questions?

Is this approach advisable (for example because it needs custom parsing from string to the needed datatype)?

Improve this question
5
  • 1
    It depends on what the properties are. What kind of things are they? How many?
    – Rocklan
    May 26, 2013 at 23:16
  • This is database design issue. If you have to add a column every time a new setting arises, that means the table is badly designed.
    – Tulains Córdova
    May 26, 2013 at 23:17
  • 4
    @user61852: How so? If you're storing a new piece of data that applies to all UserProfiles then a new column in the UserProfile table is a perfectly logical thing to add.
    – pdr
    May 26, 2013 at 23:46
  • 3
    @user61852 I'd say "poorly planned", not "badly designed". The design is, in fact, correct, but incomplete.
    – Ross Patterson
    May 27, 2013 at 1:22
  • @pdr The table should hold key-value pairs as OP suggest. Rows are cheap, columns are expensive.
    – Tulains Córdova
    May 27, 2013 at 11:42

2 Answers 2

Reset to default
1

If I understand this question correctly, then, as previously stated by @user61852, this is a database design issue.

Your 'key, value' idea is not a good idea in this instance and will certainly lead to lots of painstaking debugging. You have the full power of a relation model at your finger tips why not use it to its fullest?

From Database fundamentals (a worthwhile read!):

Data modeling defines all the required information from the real world.

You need to think about the Userprofiles table, the data it models and its purpose. If you are extending this table every time you think of something else to add you have not thought about the problem sufficiently.

Improve this answer
7
  • +1 Very true. However that doesn't change the fact that a UserProfiles table with separate columns for each aspect is exactly the right design for this.
    – Ross Patterson
    May 27, 2013 at 1:21
  • @RossPatterson ...what? No! 8-|
    – TehShrike
    May 27, 2013 at 3:35
  • @TehShrike Then post your own answer (with an example), because for storing user information in a database I tend to agree with RossPatterson...
    – Izkata
    May 27, 2013 at 4:53
  • Thank you for your answer... But from my perspective, the first part of your answer says You're doing it right!, the second one, though, tells me You're doing it wrong!
    – SeToY
    May 27, 2013 at 8:44
  • 2
    @SeToY That's correct. What he's saying is you haven't thought enough about your data, that's why your schema is so volatile. If you spend more time on your data model, your schema will be more stable. And key-value-pair tables are one of the worst SQL anti-patterns.
    – Ross Patterson
    May 27, 2013 at 10:56
1

I have previously designed a database with key value string pairs for storing user settings. The rationale for this was that the web application featured many different pages that should each be able to store page-specific settings. As pages are added and removed all the time, the only practical solution was to add the page name to the setting's key and then store the value as a string in a database.

I realize that this design is not type safe and does not scale very well. However, as long as there is only a somewhat limited number of pages per app and only a limited number of settings per page, there should only be at most a few hundred settings entries per user to search through. Even with string comparisons, performance should not really be an issue.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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