Reducing repeated information in database design

Question

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 the 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?

Answer 1

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.