normalization and relationship between tables in hospital database

[spooky30]

Hi

I am new to data base and having problem in normalizing

The problem: I have a entity category table in hospital database which has entities like doctors,suppliers,patient,staff etc: each entity consists of variable no of tables,some tables like address,phone are common to many or all and some are entity specific how do i proceed as i want to make address,phone table only once and attach them with entity with other entity specefic tables to get the full picture of entity.


EG:
Doctors linked to address table,phone table,fee table,
Staff linked to:address,phone,salary,duty roster
Supplier linked to address, phone, products etc

 

[PNGBill]

If each person has a unique address, ie their home address, home tel number etc is not shared then the normal procedure is to have a "contacts table" or staff table that would include fields for home num, mob number, postal address, office number etc. This can be be quite a long list of fields that may include car reg, spouse name but often all on one table.

You can then have a staff table and supplies table or just Contacts table with a field that will say either "staff" "Supplier". Suppliers often are not listed as persons but companies and the company table may well have the name of the contact for sales, after hours support etc. Consider this before adding supplier persons names to a contact table.

As each record in your TblContacts (staff....) holds all this information you then produce reports of all persons with offices on the 5th floor or all doctors on weekend roster.

If you do have a number of separate contact tables you can either create a query to provide a list of all the data on all the tables. Just be careful names and even primary keys may be duplicated. Doctor John Smith and plumber John Smith.

Another option is produce and update either, at fixed times or due to a certain action, a new table that combines all this data but again, you may have fields that are difficult to fit together.

I would not have a contacts table at all but produce a list of contacts from the TblStaff, TblSuppliers, TblPatients etc by way of query as required for the different outcomes expected.

You may need to draw some diagrams of your database to get a clear handle on where each part should be and how they fit together.

Trust above assists.

 

[jdraw]

Hi

I am new to data base and having problem in normalizing

The problem: I have a entity category table in hospital database which has entities like doctors,suppliers,patient,staff etc: each entity consists of variable no of tables,some tables like address,phone are common to many or all and some are entity specific how do i proceed as i want to make address,phone table only once and attach them with entity with other entity specefic tables to get the full picture of entity.


EG:
Doctors linked to address table,phone table,fee table,
Staff linked to:address,phone,salary,duty roster
Supplier linked to address, phone, products etc

Here's a link to a free data model that may be useful to you. At least it may give some insight of how things "might" be done.
http://www.databaseanswers.org/data_models/hospital_admissions/index.htm

 

[spooky30]

i would like to point out few things ,i am asking this as i want to break down contacts etc to related tables as there can be more than one phone no or more than one type of address ,emails so i want to break them down to their own tables,but relating them to each entry will lead to many similer tables like suppliers phone,doctors phone etc.
Futher it will be great help if you could point me to some refrence material on stuff tables

 

[jdraw]

i would like to point out few things ,i am asking this as i want to break down contacts etc to related tables as there can be more than one phone no or more than one type of address ,emails so i want to break them down to their own tables,but relating them to each entry will lead to many similer tables like suppliers phone,doctors phone etc.
Futher it will be great help if you could point me to some refrence material on stuff tables

Try this:
http://www.utteraccess.com/forum/Suggested-Readings-and-t373096.html

 

[The_Doc_Man]

You may be over-normalizing (yes, that is possible) or you may be obfuscating the issue by trying too much at once. That will make things bewildering faster than any other thing I know in Access.

Let me do this for a simple subset of what you have.

First, having separate tables for people in various roles might or might not be what you want. I don't know for sure. But let's say you look at TWO of the tables: Doctors and Staff. Combine the two tables and have a separate "attributes" table (or set of tables).

Instead of

tDoctor : DocID, DocName, DocSpecialty, Docthisthatandtheother
tStaff: StaffID, StaffName, StaffDuties, Staffwhatever

and then linking multiple phones, multiple addresses, etc.

How about

tPerson: PersID (primary key), PersName, other personal data
tRoles: RolePK, PersID (as foreign key), Rolename (or role code or both), PrimaryYesNo
tAddrs: AddrPK, PersID (as FK), address, PrimaryYesNo
tPhone: PhonePK, PersID (as FK), phone number, type (either coded or just say CELL, WORK, HOME, etc.), PrimaryYesNo

OK, now you can just use a big JOIN query (perhaps multi-faceted) to find each person joined with their primary role, primary address, primary phone, etc.

This next part is where new Access users get confused, so bear with me.

From the person side, you have a one-to-many relationship because one person could have multiple roles, multiple phones, multiple addresses, etc. From any one of the individual "attribute" tables, that is a many-to-one relationship to see a list of, say, doctors. You do a query on the role table where the role name is "DOCTOR" (or however you use this feature) JOIN across the PersID to the person table to get the name.

You can REALLY get kinky if you must because in a query you have the ability to define a TEMPORARY relationship. So if you wanted each doctor's business phone, you would create a temporary link to the Phone table across the PersID and with other criteria (such as PhoneType = "WORK") to narrow the list of choices. That way you have a unique linkage for each attribute as long as you remember to qualify each attribute linked via the PersID that is a Foreign Key in the Role table. Note that if you DON'T qualify that join to narrow it, you get a permutation join of EVERY POSSIBLE COMBINATION of attributes in any table that has multiple records per person.

Sometimes by keeping things separate we organize them better. Sometimes, it isn't better at all. I think your case represents overseparation - but I could be wrong! Now, let's be clear - this is YOUR problem to solve. We can only suggest solutions, and if you have a business reason to not take a suggestion, so be it.

 

[PNGBill]

A side idea that may help to get your head around this.

If I go to Microsoft Access in my All Programs menu I have 3 choices - Blank Access database, Access database wizards, pages & projects plus open an existing file.
Try selecting wizards and select all the available options. Let the wizard make the database.
go to tools, relationships and you will see 3 tables and how they have id's that are used in each table so queries can then use the data to provide reports and forms as you need them.

Your Database would not be as simple as this because you want to know about Doctors, Nurses, Technicians etc but however many tables you have they all must join and the relationship needs to be defined.
These questions should be asked, understood and solved before the next process of making queries, reports and forms takes place.

A good rule in deciding how many tables you should have is to think about what data is unique to that table.
eg a TblStaff would have StaffID (Primary Key) FirstName, LastName, Office, etc and should include home address and contact details because normally only one staff member would live at 15 rosewood street and have that home telephone number.

You would not be wrong then to have a table TblOfficeDetail that held all the office numbers and there respective work stations, pc's, telephones, FireExitID, etc.

Then TbleOfficeDetail and TblStaff would have a relationship (line) linking the field "Office" in TblStaff to TblOfficeDetail primary key in a one to many relationship ie one office but many staff.

This process would then go on until you eventually complete the list of all your required data that can not be produced from existing data. ie, you would not have a field anywhere in your database to hold the Full Name of a person as this would be generated as you require it by a query.

I just built a small Fixed Asset Register to replace an Excel File that did the job with one worksheet per year.
This one worksheet has been replaced with five tables in access. Of course, access does more and better but the point is five tables were needed.
Also 25 queries, 7 forms, 3 reports and two macros.

Some of the experts here may well cringe at the number of queries but I find it easier to do tasks step by step and then you find small errors to fix rather then bigger issues and some of the step by step queries can be used by other queries.
eg a query to select all the Contacts with their related full names, addresses etc could be used by other queries to just give a list of doctors or nurses, air conditioner repairer etc.
Don't be afraid to have a few tables just try and have each table hold data that is unique to it's role and has a purpose in the database.

Trust the above is not boring.

 

[spooky30]

Thanks doc man the problem i am facing is with concept
this is the very best answer i got from various forums but there is one problem that
each role may have various sets of tables attached to it like if supplier than supplier related tables like product price etc,in role of doctor visit timings etc and these tables are variable in numbers.....how do i achive that

 

[The_Doc_Man]

The key is that all tables of "special" nature - such as the supplier table - are simply one-to-many (or many-to-one) based on how they are being viewed. But that is misleading. It is perfectly OK for any given 1:many table to actually be 1:none in a couple of cases.

So you define the secondary attribute table. It is just like any of the other tables. If you have no attribute entry to the primary person table for a given person record, it simply means that the attribute doesn't apply to the person. This is an example of what is called "SPARSE" table design.

If this confuses you, consider your phone-number table. So what do you do if the person happens to not have a cell phone. (Yeah, I know - heresy... but suppose for the sake of discussion.) The SPARSE viewpoint says if you don't have a cell phone, you won't have a cell phone record. The DENSE viewpoint says you have a cell phone record but it contains a flag that says "NONE." The DENSE viewpoint takes up more space. The SPARSE viewpoint takes up less space but might sometimes increase the amount of time it takes - by a very small fraction - to develop a proper recordset when gaps exist.

In practical terms, this means that when you are coding up something in a SPARSE environment, you do a DCount check to see how many records exist with the particular set of attributes. Then your code does whatever it needs to do based on the knowledge that record CELL for user 12345 does or does not exist.

To make it show up correctly in queries, you have a couple of choices. One that is often used is to change the INNER JOIN normally built by Access to become an OUTER JOIN to allow a NULL field to show up for the no-child-record case.

In the query, instead of just showing a null field, you make an expression using NZ([whatevername],"NONE"). You don't want to multi-layer this if the innermost layer is null-prone, but sometimes this is all you need to do the manipulations you need for reports and such. Beware that OUTER JOIN situations can sometimes lead to queries that cannot be updated. But for reports, that might not be an issue.