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: 297
        
relational-database
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