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.

I need to make a decision for database structure on whether to separate one-to-one relational columns into multiple tables and link with one relationship id or just add all columns into one table.

The number of columns would be around 45 and I need to sort data on different columns on different query (one sort per query).

I will be using MyISAM storage engine.

Furthermore, there will be millions of data in the table(s).

share|improve this question
1  
What do you see as the advantages of splitting the table? –  David Aldridge Jun 5 '13 at 9:08
add comment

3 Answers 3

Another thing to consider in deciding to split the tables or not is the width of the table if you put them all in one table. Many databases will allow you to define a table where teh total length of all the fields is wider than the total record length allowed. You cannot howeever, put data into a record that woudl exceed the width. Therefore, if you are going ove the limit, it might be wise to split the table. Further, most databases can store and retrieve data more efficiently in tables that are less wide. So if the fields you want to split out are not always going to be queried with the fields in the orginal table and the table will be large, it might be in your best interests to split the tables out.

Another thing to consider is if you are in a 1-1 relationship now, is it likely or possible that you will want to change to 1-many later? For instance suppose the fields you want to move are out are address fields. Maybe now you only need one address, but having them in a separate table makes it much easier later to have multiple addresses.

If you split the tables, make sure you define a unique index on the FK to preserve the one-to-one relationship.

share|improve this answer
add comment

If the relation is one to one, that sounds like there is a functional dependency in both directions, i.e. the columns represent pieces of information for a given item.

In your conceptual model, you have entities, and the columns represent the information belonging to such entities. In my opinion, unless there is a very obvious reason to separate information into different entities, these columns should be in the same table.

share|improve this answer
add comment

If the relationship is really one to zero or one, you may be dealing with a class/subclass situation. In ER modeling this is known as generalization/specialization.

If this applies to your case, I recommend you visit the following tags defined in Stack Overflow, and read up on the info stored in them.

[single-table-inheritance] [class-table-inheritance] [shared-primary-key]

If your case is not a class/subclass situation then this answer won't help you.

share|improve this answer
1  
Your links are to the dba.se tags. The 3 tags you have are not defined here, omly at SO. –  ypercube Jun 5 '13 at 11:51
    
Yikes. Thanks. Will edit. –  Walter Mitty Jun 5 '13 at 13:30
add comment

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.