Reducing repeated information in database design

I am designing an application that when users create a new account, their account is populated with several hundred rows of data (predefined options, settings, etc). Most of the users will rarely, if ever, change the information contained in these tables. Are there any good design patterns to reduce the amount of duplicated data in the database tables?

Some ideas I had so far:

• Keep the predefined options in their own tables, and only save custom options to 开发者_如何学JAVAthe user tables. This would seem to reduce the overhead, but I can see the database calls getting really tricky.

• Create a new database for every 100 users or so, with a separate database for accounts that points to the users database. This is, well, not optimal.

• Idea 3?

Creating a new database for 100 users sounds like a crazy and inefficient idea.

How much data are we talking about and why do you want to reduce it? Even if you have millions of users, a few million rows is not a very large table - unless the data for each user is multiple megabytes in size. Well chosen indexing ought to be sufficient to optimise the user table. However, it could make it simpler to modify the default settings for all users if you only store user settings when they change the defaults.

NO, dont create a new Database per any number of users.

Stick to option 1.

Or even setup UserGroups with selected options activated/rules applied.

UserRoles/Rules can get tricky, but you dont want to shoot yourself in the foot by maintaining multiple tables/databases for new users.

You need to implement a Users to UserGroups to UserRoles and UserRules

Also, If you ever see duplicated values per row, you need to look at Database normalization

It really depends on how many tables are being populated by user options, but if the great majority of the time, the user options are just the same, default value, then you could just put them in their own tables, and just use views to make pulling the data out seamless. (so option 1)

look at the statistics of use of the fields and cluster the ones together that are commonly changed so that if there is no record for that user for those less often changed fields that they fall back to the defaults

i think there was a way with a join or something to do defaults like that

Definitely, create a base profile (or possibly a group of base profiles, for example based on nationality, or any other meaningful partition) where all the default rules and values are represented.

At the user-level create only "exceptions", i.e. only values that have been changed by the user, and always access the profile as a join between the relevant base profile and the user-specific exceptions (that hopefully will be empty, i.e. non-existing in the DB).

The only concern is that the various defaults should be grouped in a number of smaller (in the sense of number of fields) tables, otherwise you won't get any benefit from this approach (i.e. if you put all the possible settings in a single table record you will have to duplicate everything as soon as the user changes just one field, and this will give no benefit at all).