Dan,

You havent' said anything at all about the size of the tables or the speed requirements you have for your application.

I've seen lots and lots of newsgroup questions stemming from poor data design, and few stemming from having to use good design.

Data warehouses have denormalized data that is optimized for reporting purposes, but the data itself is relatively static, such as montly totals, etc.

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

You make this sound like it's terribly complicated to join three tables via persistent relationships or SQL. Do you have an ease-of-programming maintenance goal for your application? What's simpler to you, typing a little extra, or having to leave good documentation for the next programmer as to how to deal with a non-normalized data design and keep the data synchronized properly?

Finally, be sure you're familiar with the requirements of HIPAA unless the lifetime of your application is shorter than April, 2003. From what I know of HIPAA, in order to prove that you have maintained privacy you'll need to track who looked at each record that was retrieved. The greatest breach of privacy is staff with appropriate permissions to the data using them inappropriately, such as looking up their neighbor's health information.



>Steve-
>
>I appreciate you voicing your views. I've read a lot on "Data Normalization" and I guess I wanted to here the views of FoxPro programmers on the UT.
>
>One pitfall I would like to avoid is over-normalization. The two symptoms of over-normilization I have found is too complicated and too slow.
>
>That may not be the case here but if I'm going to raise the level of complexity, I better have a good reason.
>
>Higher complexity usually equates to increased maintenance hours.
>
>>>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,