Split a table due to clearness, maintainability?

Question

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           [email protected]   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?

Answer 1

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