Split a table due to clearness, maintainability?_问答_开发者

Does it make sense to split a table just for the sake of clearness, maintainability?

I have a classic user table, which amongst others stores data like the following

userid  name        userName        email            lastlogin      
-------------------------------------------------------------- 
1       Buffer      Stack           s@overflow.com   1312565858

Additionally I need to persist settings on a per user basis. These settings will cover mostly boolean fields and might look like this.

quickSave   showInfo    showOnlineStatus    sendNews         sendRemainder      
--------------------------------------------------------------------------- 
0           1           1                   1               开发者_如何转开发 0

That being said, I see at least the two (usual) options

add the individual settings directly to the user table
create a separate settings table and persist the relation

My tendency to structure things leads me to latter.

Question

Is it a valid approach to split tables, if they are strictly 1-to-1 related like the above and if so, is there a threshold, when to do so?

My preference is the base my spilts on read/write activity.

For example

If your user table is only going to WRITTEN infrequently but READ frequently the I would separate it from the settings table which may be WRITTEN more frequently.

This is more critical if you are using myISAM tables as they have table level locking. InnoDB uses row level locking ( but you can still lock tables as a whole ) but still its nice to be able to have the option move storage around based on read/write frequency

I would suggest that "it depends" is the only completely true answer that can be given.
- The question is kind of like asking "is 6th normal form better than 5th normal form?"

The benefit of seperate tables is predominantly flexibility:
- The ability to make design changes with minimal impact

For example, you may want to start maintaining old values as well as current values, the less tightly coupled everything is, the easier to make the change.

But the extreme version of that would be to have a seperate table for every single field. And I can't see many people suggesting that That would be a good idea. Especially from a maintainability stand-point; it may be flexible, but having a billion tables is far from easy to understand.

My basic rule of thumb about when to use a seperate table is when the fields can be logically considered significantly different. Details about a user (name, address, etc) and their applciation settings (quick save, etc) seems to fit that bill well enough to me. It may not matter here, but you then potentially avoid contention where one process is trying to process user addresses, and another is updating user application settings.

That said, if you find that you always join two tables together before the data is usable, it's a strong indicator that they shouldn't be kept separate. And I suspect you may use these tables by looking up the user's login details in one, and joining to the other.

My personal choice in your case, from the narrow perspective of this question?
- Keep them together in one table
- Until you ever find a reason not to

You can always separate them out later, and recombine them in a view, to maintain code compatibility.

I would choose a third option:

Create an authentication table that stores information like lastLogin, userName, etc. and THEN add all these columns to the user table.

This information does describe the user, so in the interest of maintaining database entities, I would add the columns to the user table.

On the other hand, if there's a well defined boundary around an entity you could describe as settings, then there's nothing wrong with 1-to-1 relationships.

Database theory can get really out of control in a hurry. The answer is to be reasonable when making a decision.

True, hardcore DBA's would argue that an email doesn't belong in a user table because the user could have multiples....and could change....and you could have to keep a history....etc. In reality, it's silly. If your app only requires one email address, it works for you. The point is, there's more to making these decisions than solely theory.

Similarly, in actual practice either solution you proposed is going to work. None is going to have a dramatic difference on performance. If it was me, I'd likely save it to a separate table that has all user preferences to include other UI elements as well. Again, it's up to you and your needs.

You have a trade-off. You currently have a series of things that are in a one-to-one relationship to a user. By keeping them in the same table you make them easier to query but more difficult when you want to add another field. By adding a settings table, you may make the data more difficult to query and possibly create a performance nightmare depending on the design.

You could go about creating the table in two ways, making a table that is in a one-to-one relationship to the users. This is fine if your users table is already very wide and you don't need these settings in most queries of the users tables. This can also be good if you have a situation where things are 1-1 now, but you expect them to become 1-many later. You still have to alter the table if you need to add a new setting though.

The alternative method is to create an EAV table that contains something like Userid, SettingType, Value. This is a very poor method for storing for querying most data. Now instead of querying one table, you need to query the first table and join to the second table multiple times to get all the data. And you may not even know in advance how many times to join. This structure should be used sparingly and never for settings that you know you will need in advance. It's proper use is for client-defined fields. And then only if you expect to rarely use it. It is a performance killer.