>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.
>

Dan,

Data Normalization rules are the guidelines you use for making these types of decisions. I prefer to go to the 3rd normal form, at a minimum. The 3rd normal form says that all columns (fields) must totally depend on the primary key. The first method you propose violates 3rd normal form, because you would have some fields for employees, and others for patients.

So, my vote would be to split it into three (or more, if necessary) tables, using surrogate (or, non-meaningful) primary keys in each table. Of course, opinions vary widely on this subject, and there are individual cases which may justify breaking the rules.

Do a search on google.com for ‘Data Normalization’ and you should find some helpful information.

HTH,