Consider we have a large set of statistical data for a record; e.g. 20-30
INT columns. Is it better to keep the entire set in one table as they all belong to a record OR creating a another table connected with a one-to-one relationship.
The advantage of the former is to avoid
JOIN and have a quick access to all statistical data for the corresponding record.
The advantage of the latter is to keep the column tidy. The first column is read-intensive, and the second write-intensive. Of course, I think it has no significant effect on the performance, as I use InnoDB with row-level blocking.
In general I want to know if it is practical useful to separate different sets of data for a single record?
If it fits within the rules of normalization, then 1:1 relationships can be normalized (by definition!) – In other words, there is nothing about 1:1 relationships that make it impossible for them to obey the normal forms.
To answer your question about the practicality of 1:1 relationships, there are times when this is a perfectly useful construct, such as when you have subtypes with distinct predicates (columns).
The reasons you would use 1:1 relationships depend on your point of view. DBAs tend to think of everything as being a performance decision. Data modelers and programmers tend to think of these decisions as being design or model oriented. In fact, there is a lot of overlap between these points of view. It depends on what your perspectives and priorities are. Here are some examples of motivations for 1:1 relationships:
You have some subset of columns that are very wide and you want to
segregate them physically in your storage for performance reasons.
You have some subset of columns that are not read or updated
frequently and you want to keep them apart from the frequently used
columns for performance reasons.
You have some columns that are optional in general but they are
mandatory when you know that the record is of a certain type.
You have some columns that logically belong together for a subtype
and you want to model them to fit well with your code’s object model.
You have some columns that can only apply to some subtype(s) of an
entity super-type, and you want your schema to enforce the absence of
this data for other subtypes.
You have some columns that belong to an entity but you need to protect
these particular columns using more restrictive access rules (e.g. salary
on an employee table).
So you can see, sometimes the driver is performance, sometimes it is model purity, or just a desire to take full advantage of declarative schema rules.
The major reasons why you would use a one-to-one mapping to break a large table into two are for performance reasons for example:
a) The table has binary/clob/blob data in a frequently accessed table hence slowing down performance since the large columns are handled differently.
b) The table has many columns which are accessed by different queries, hence the performance is degraded therefore you would move related columns into a separate table to improve on access performance
However having many integer columns does not justify the additional effort of breaking up the table into separate tables and having to query them.