should I denormalize for user profiles?

Question

Assuming the choice is to stay with relational model, how are tables structured in the case of large User profiles? All the examples I see consider only simple cases, such as a "profile" containing name, email, and a few other columns.

Say the app is a Life Dashboard, where each user profile has all kinds of disparate data, from favorite movies, to money spending statistics, to pictures. And since its a dash, we want to return all of it to the single page app upon user login.

Is this a use case for denormalization? without denormalization, it would require multiple queries just to return the first request: an authorization query, followed by one for each of the potentially 10-15 different normalized tables on userID. is this common? or in this case is denormalization or even switching to NoSql the preferred method? or case by case depending on how whether we want each entity in the normalized table represented and queryable?

Answer 1

Normalization is not done to make reading data easier or more efficient. Normalization is done to make changing data simpler and less risky. It's when you insert, update or delete data that you will see the benefits of normalization.

Therefore, if you are going to update your data much less frequently than you are going to read it, for example, if your data almost never changes, then optimizing for reading might make good sense. However, if you are going to be adding new data and changing existing data pretty often then you will be better off with Third Normal Form (3NF) as your starting point for your database design.

Answer 2

It seems like you have a number of lists for each user. There should be separate tables, with separate queries for each list. Look into database normalization. Third Normal Form (3NF) should be your minimal starting point. Data in lesser forms is likely to become inconsistent over time.

The design is likely to end up with a profile table and a number of subject area child tables.

Trying to retrieve all the data in one query will create significant issues on retrieval, as you will need to deal with Cartesian Products in the result set. You may find your design ends up with different views where views match to database tables. If you generate reports, then sections are likely have a high degree of correlation with tables.