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?
Best Answer
Number 2 would be recommended by most people, myself included. Not so much because they are different 'things', but because you are modeling a relationship between the two.
While the first option would also work, it would only work for those cases when a client has one and only one employer, and each client has a different employer.
It would not work in cases where a client has more than one employer. And if more than one client has the same employer, you'd have the employer listed twice (or more) in the database. That would lead to repetitive data, and complexities when trying to update their details. Better to break them into two tables.
Another approach you might also take is to have 3 tables - one for the distinct list of individual clients, one for the distinct list of employers, and then another that connects clients and employers. That would allow you to model cases where clients have more than one employer, the same employer has more than one client, and cases when a client has no employer.