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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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?