For example these tables contain many repeated columns.

enter image description here

Does this affect normalization or this is a common practice? Would it be better to create a separate 1 to 1 table like this. enter image description here

This is 1 to 0..1 relationship, not 1 to many.

  • Please see my answer to this question and also this one – Joel Brown Apr 16 '17 at 12:05
  • Your answer is about one to many relationship. My question is about one to zero or one. – Vitalii Vitrenko Apr 16 '17 at 16:42
  • The principles of normalization are identical with respect to 1:0-1 or 1:0-M. A child table is a child table, whether it's an only child or not. – Joel Brown Apr 16 '17 at 18:34

Your example does not violate 1-NF, it is not an example of a repeating group. Whether you should use a "super type" (in your case PERSON), is more a question whether something can be related to both an EMPLOYEE and a VENDOR (PERSON is probably a poor choice of a name since a VENDOR is likely to be a non PERSON).

Side note:

Speaking of naming conventions I prefer plural, there are arguments for and against plural as well as singular names, but to me a table represents a set of EMPLOYEES so I prefer that. Attribute names like id are to vague IMO, it is an id of something, i.e. employee_id. Then that attribute name can be used for everything that represents an employee_id throughout the model.

| improve this answer | |

Repeated data violates first normal form itself.

For an example, to be in the first normal form, you cannot have columns like phone1,phone2 in the employee table.

However in your scenario, having the same columns for two tables does not violate first normal form as long as you have a single column for a particular property.

Your approach to have a common table should be based on your application requirements.

External Source

Hope this helps :)

| improve this answer | |

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