Hi,

I'm designing a database to hold monthly patient volume data for a hospital. The hospital has specialties like Medicine, Surgery etc, but on the other hand, the hospital also has services like outpatient, inpatient, procedures etc. The problem is that each specialty is involved in more than one services, and each service also contains components from different specialties. E.g. Medicine department has patients coming in as inpatient or outpatient; while the outpatient department itself contains clinics of Medicine, Surgery and so on.

I am confused at what would be a good structure. Should I have separate tables for Medicine Surgery etc and put services as fields in there, or should I do it the other way round?

Any help would be appreciated.
Thanks.

Without assignment in full we cannot say.

Of course all services cannot service all departments. So remembering this, it is better to have 3 tables. One for the medical departments, the next for the services and finally one for valid combinations and finally a transaction table of some sort.


End