Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
'Normalized' means first normal form (1NF) and is a fundamental requirement of the relational model. 'Fully normalized' means 5NF or higher. Your proposed 'one-to-one relationship' table has a better chance of being in a higher normal form (possibly even in 6NF) than your current one because it is decomposed! What normal forms does your existing table satisfy? –  onedaywhen Mar 22 '12 at 14:29
    
@onedaywhen Like many others I do not follow normalization step by step, as sometimes de-normalization is also helpful. In general, the entire database should have a normalization level between 3NF - 5NF (I always have problem with 4NF!) –  All Mar 22 '12 at 15:32

2 Answers 2

up vote 13 down vote accepted

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.

share|improve this answer
    
You have some subset of columns that are very wide and you want to segregate them physically in your storage for performance reasons. How does segregating them improve performance (assuming the columns are always accessed every time the main table is)? –  Gili 19 hours ago

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.

share|improve this answer
    
very good point for clarification of the issue! –  All Mar 22 '12 at 15:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.