Should i separate ContactInfo (sub-entity) from MyTable (main-entity) or Not

database-designnormalization

I am designing a database of a school management system. I have one to one relationship of MyTable with ContactInfo table. I am confused whether I separate contactinfo from MyTable or not. ContactInfo record will be single against each MyTable record, but ContactInfo can be updated many times.

Should i merge ContactInfo as
Merged two tables

or develop a separate ContactInfo as given below
enter image description here

Please justified your help with the solid arguments or rule.
Thanks for your help.
Note: ignore the blur fields.

Best Answer

I do not know exactly what you mean by 'updated many times' but I presume that you mean that the same ContactInfo can be present in multiple MyTable entries. In this case I would split it but put the ContactId in the MyTable entity. This way you are sure that all ContactInfo is the same everywhere it is used in MyTable.

The rule is that you put all fields that are identified by a single primary key in a separate entity. This way you minimize the number of updates when one of the fields that 'belong' to this primary key changes.

If 1 ContactInfo entry has only 1 MyTable entry and 1 MyTable entry has only 1 ContactInfo entry then the separation is not necessary. The only advantages are:

  • If the length of an entry is big there is less chance that an entry does not fit in the physical block any more and needs to be 'moved' to an other physical block by the RDBMS.
  • When you rarely need to select the ContactInfo and MyTable at the same time that (with a lot of rows) the fetch in of the rows goes faster since more entries will fit in a physical block.
Related Question