when should a database table be broken into multiple tables with relations?

Posted by GSto on Programmers See other posts from Programmers or by GSto
Published on 2012-05-31T17:52:37Z Indexed on 2012/05/31 22:50 UTC
Read the original article Hit count: 189

Filed under:

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?

© Programmers or respective owner

Related posts about relational-database