User characteristics database schema

Question

I'm really battling an issue where I have a Users table that has a growing number of user characteristics (regligion, smoking preferences, etc). The strategy I've used thus far has been to add a column for each preference that keys off onto another table.

For example, if User XYZ has a RelgionId of 3, that could mean they're Christian. At runtime, if I need their religion, I join onto another table.

This strategy has worked so far. However, I'm getting concerned about the number of columns in the tables as the number of preferences is increasing. Also, this strategy leads to many joins if I need to get all values for a single user.

I'd like to find out the most normalized way of representing this data. Anybody have any ideas?

Answer 1

I'd like to find out the most normalized way of representing this data.

Well, from what you describe, you seem to have quite a normalized database. What you are looking for if you want to reduce the number of joins is denormalization.

For instance, if you want to access a subset of those user preferences with a smaller number of joins, you might want to cache them in a UserDetails table, and link that in the User table with a UserDetailsId foreign key.

This might actually be feasible in case you have a subset of seldom-changing values (for instance one's religion does not often change).

The drawback is that in case one of these changes you might have to change the info in two places (depending on if you want to also keep the normalized version of that data or not).

I hope this helps. Feel free to ask for additional clarification.