Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
An obligatory link to normal forms: andrewrollins.com/2009/08/11/… Yes I think your schema needs to be normalized at all times (except when you explicitly denormalize it for performance, and you know full well what you're doing). Normalized schema saves you a lot of data consistency headaches. –  9000 May 31 '12 at 18:31
2  
As a meta-comment to the process at hand: I don't think this is MySQL-specific at all. These decisions should be made long before implementation in the physical model and rather during design on the conceptual and logical models. –  Jesse C. Slicer May 31 '12 at 18:48
add comment

6 Answers

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.

share|improve this answer
    
designing for a many to many relationship is a bad idea if it isn't one though. –  Ryathal May 31 '12 at 18:37
add comment

One should always strive in one's designs not to make unwarranted assumptions that may come back to bite one in one's backside.

In this day and age, in the United States at least, the 1-employer-per-employee assumption is VERY unwarranted. They may very well have ZERO employers, they may well have up to three employers.

With that in mind, schema 2 is far better, as it does not QUITE lock you into a 1-1 assumption.

share|improve this answer
add comment

Option 2 is the correct approach. You have your Foreign key backwards though the client table should have an EmployerID field since you said that a client will have only one employer and many clients may have the same employer. This is a database normalization issue, and the goal of database normalization is to reduce insert, update and delete anomalies that can happen, and can be very costly, if tables are not properly normalized. Normalization can slow down select statements though on large tables, in most cases the difference between normalized tables and non normalized tables will be so small its unnoticeable.

it is possible to mitigate the hit to select queries by creating views, but this may not be possible depending on frequency of updates. The general rule of thumb for database design is to normalize as much as possible, and then denormalize as performance dictates.

share|improve this answer
add comment

In my opinion, unless you have some sort of reason to abandon good relational database practices (like extreme scaling needs or the like) you should follow good relational database practices even if you think the today's requirements don't force it. Having said that, I think your individual tables should contain only the columns that pertain to the concept you are trying to model with that table.

An employer is definitely a separate concept from the client, so mixing them together in the same table is ... problematic. Can it work? Sure, for a while. But it will eventually become a problem.

You are heading in the right direction by removing the employer data from the client data, but I would go one step further and create an Address table, too. Then, your schema would look more like:

Client Table
------------------ 
id int
name  varchar(255),
email varchar(255),
employer_id int,
address_id int

Employer Table
---------------------
id int
name varchar(255),
phone varchar(255),
address_id int

Address Table
---------------------
id int
address varchar(255),
city varchar(255),
state char(2),
zip varchar(16)

Now the tables are much more straight-forward.

And, when you eventually need to add more information (think 'delivery_instructions' for an address or some employer-specific attribute) you'll have an appropriate place to put it.

share|improve this answer
1  
Just what I was going to add. As other have noted, it may be appropriate to use a join table between Client and Employer. This should be relatively easy to add later if needed. I have also used join tables to Address to allow multiple addresses, or record changes in address over time. –  BillThor Jun 1 '12 at 23:25
add comment

Why would you be making this decision? Either the client wants/needs the ability to:

  1. Assign multiple employers to each client.
  2. Have consistent data on an employer (i.e. change phone number in one place for all employees)
  3. Track employment over time (similar to multiple employers, but must concept of "current employer").
  4. What if they have a need to expand on the data for employers? At some point it would be better off in its own table

or they don't. How important is address information? What type of reporting/aggregating do they expect?

If you think this is complicated, wait until you findout this really requires a third table to create a many-to-many relationship.

share|improve this answer
add comment

As usual...

It Depends

Both structures could be valid, depending on what you are going to do with the data.

If you just need a 'dump' table for a fast report of the current client+employer information, then the first arrangement is fine.

If you are modeling a simplistic subset of the real world, where every client has zero or one employer, then the second arrangement will suffice (though client should point at employer and not vice-versa).

If you are maintaining a more 'real world' model, where time matters, then client and company could be separate tables, with a third table for the is-employed-by relationship which includes start-date and end-date fields; people do change employers from time to time ;)

If you want a minimal, nonredundant (even object-oriented) model, then you'll note that you are storing almost exactly the same information about client and company, which could be generalized to a single Company table. Another table would note that some of the Companies are your clients, while another table would note the employed-by relationship.

The latter is arguably less redundant and more 'real world' than the other models, but can be more difficult to query and maintain.

Which brings us back to: what, exactly, are you going to be doing with this data?

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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