We have a website that will have basically 2 types of users. Employees and Patients.

Both these users have a login and password. The Employees will do reporting and admin on the site. The Patients will basically using the site to view their records.

The two are very similar as far as to what information we need to store for this particular app.

There are two ways to go. One have a usertype field and just one user table. This is ok but we now have to look at the usertype field every time we SQL into the User table. We will also have some fields that will not be used by both user types.

The other solution is to split into three tables. Have a user table, which just holds the Login information. Have a Patient table that holds information specific to the Patient. Have an Employee that has information specific to the Employee. But now we have to maintain multiple tables. In some cases we are doing multi-table joins instead of a simple SQL from one table.

I figure you would have to handle this type of question on a case by case basis. The question is what are the guide lines for making the decision to split the tables or keep just one table.