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).
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 the total length of all the fields is wider than the total record length allowed. You cannot however, put data into a record that would exceed the width. Therefore, if you are going over 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.
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.
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.
