MySQL database design question

Question

I'm trying to weigh the pros and cons of a database design, and would like to get some feedback as to the best approach. Here is the situation:

I have users of my system that have only a few required items (username, password). They can then supply a lot of optional information. This optional information continues to grow as the system grows, so I want to do it in such a way that adding new optional information is easy. Currently, I have a separate table for each piece of information. For example, there's a table called 'names' that holds 'user_id', 'first_name', and 'last_name'. There's 'address', 'occupation', etc. You get the drift.

In most cases, when I talk to my database, I'm looking only for users with one particular qualifier (name, address, etc.). However, there are instances when I want to see what information a user has set. The 'edit account' page, for example, must run queries for each piece of information it wants.

Is this wasteful? Is there a way I can structure my queries or my database to make it so I never have to do one query for each piece of information like that without getting my tables to huge? If i want to add 'marital status', how hard will that be if I don't have a one-table-per-attribute system?

Thanks in advance.

Answer 1

I am currently reading the book "Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (3rd Edition)" and the book specifically mention this point.

you should make one table to represent one object type, so make all user attributes in one table. all machine attributes in one table. all payroll attributes in one table. all customer attributes in one table.

Answer 2

I would seriously consider the entity-attribute-value model. A one-table-per-attribute approach would work but may be messy to maintain.

The EAV model would give you the most flexibility and allow you to easily store additional fields without having to modify your database schema. It would also allow you to easily pull all of the user's optional information in a single query from a single table.

Answer 3

You use joins to link the tables together and so you still only have one query.

You don't need a spearate table for each attribute, only separate tables when you are going to havea one to many realtionship. For instances, people have mulitple addresses and phone numbers, it makes sense to separate them out into separate tables. But you can only ever have one marital status at any one time, so it can go into the users table easily.

Answer 4

You can use views to build up the combined queries.