when should a database table be broken into multiple tables with relations?
- by GSto
I have an application that needs to store client data, and part of that is some data about their employer as well. Assuming that a client can only have one employer, and that the chance of people having identical employer data is slim to none, which schema would make more sense to use?
Schema 1
Client Table:
-------------------
id int
name varchar(255),
email varchar(255),
address varchar(255),
city varchar(255),
state char(2),
zip varchar(16),
employer_name varchar(255),
employer_phone varchar(255),
employer_address varchar(255),
employer_city varchar(255),
employer_state char(2),
employer_zip varchar(16)
**Schema 2**
Client Table
------------------
id int
name varchar(255),
email varchar(255),
address varchar(255),
city varchar(255),
state char(2),
zip varchar(16),
Employer Table
---------------------
id int
name varchar(255),
phone varchar(255),
address varchar(255),
city varchar(255),
state char(2),
zip varchar(16)
patient_id int
Part of me thinks that since are clearly two different 'objects' in the real world, seperating them out into two different tables makes sense. However, since a client will always have an employer, I'm also not seeing any real benefits to seperating them out, and it would make querying data about clients more complex. Is there any benefit / reason for creating two tables in a situation like this one instead of one?
