>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.
Depends on the expected number of hits per record and per field, i.e. how often are you going to need them, and how often would you have blanks there.
I'd rather go with the third solution - one narrow and minimal table of all of them together, and then type-specific stuff in other two tables. The time you spend on coding should pay by the time saved by always having to filter out unnecessary fields or records from one big table.
I once had a patients table I had to split in two, 1-0/1 related. They were originally one table, which had more than a hundred fields. After a couple of months, it turned out that only about 2% of the records had anything beyond tenth field, so I created a main table with just the first ten, and the other one with the rest, just for those who had some data there. It did take me a few days to do, but in the end the system ran much smoother and faster.
