Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2007
    Posts
    12

    Question Unanswered: Data Model - Too Normalized

    The size of this database has me very intimated and I want to make sure I have the table structure set up so that I don’t suffer a set back down the road. I am concerned about the structure and whether or not I have it too normalized (is there such a thing?) I have been using Access for 6 - 8 years and I am now in charge of creating this very large database for the hospital that I work. The database is for the switchboard operators to be able to access information that is requested by callers. I have attached a zipped .bmp file of the data model. Would someone please take a look at it and let me know what you think. You will see it is color coded:
    Yellow = all Lifeline and Area Company Information – this would be things like Teen Pregnancy Centers, Suicide Hotline, Crisis Prevention, Drug Stores, Nursing Home, Funeral Homes, Churches, etc.

    Orange = all FTMC hospital information – this includes departments, employees, hospital hours (i.e. gift shop, visiting hours) Pavilion Phone Numbers, etc.

    Green = all Physician information – this includes the practice name they are associated with, Practice (some practices can have several locations), Specialty of the practice and physician, Nurse Practitioners and credentials, etc.

    Pink = Non-specific information – this is information that can pertain to the other 3 above areas
    Attached Files Attached Files

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    When I think about too-normalized, I look at what fields of information is typically returned in report or query. For example, separating City, state, and zip in separate tables is, in my opinion, too-normalized. How many times would a customer's full address be returned and why would I want to link 3 tables together to return a single address? Returning information in 1 or 2 tables linked together is a lot quicker than returning information in 4-5 tables linked together.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    May 2007
    Posts
    4
    I just took a quick look at it and it seems pretty good. I must admit that it truly was a quick look, mostly for the fact that being "out of the loop" for this project, it's somewhat difficult to actually envision how you plan to set this up. So I mainly focused on your normalization, which is what this thread seemed to indicate you wanted.

    Yes, it is possible to over-normalize a database. For example, it's very likely that several of your entities are located at the same address, so perhaps create an Address table. And while you're at it, several addresses may be located on "Main Street" (or some other common street in your area). Thus according to normalization rules, you could have a Street table that the Address table points to.

    The problem becomes that although data duplication may exist without normalization, too much normalization may slow down the database. This is where the subjective feelings of a DBA comes into plays (ok... so there may be metrics that can be used to help objectify the decision, but usually gut-instinct seems to work pretty well).

    Going back to that address / street table hypothetical, it may be a viable normalization if, say, the hospital was located on some type of a campus. There, many of the employees would all be working in the same building (so the address table makes sense) and several of the hospital's buildings could all be lined up along one street (so the street table would also make sense).

    On the other hand, if this database was for a nation-wide registry, then this normalization may not be ideal. Although it may be possible for several entities to be at the same location, it may be more likely that each will be at a separate location. So any JOINs of the two tables may slow down queries, negating the advantages that normalization would bring you.

    Also consider the fact that data items are meaningless ("10" could be someones shoe size, room number, or diving score); it only provides information when in context. Given the above, a streetName field with "Main" is still meaningless. Is this Main St. in Buffalo, NY or Buffalo, WS (is there such a place?)? And what happens if one city decides to change the street name to something else; there weren't any Martin Luther King Blvd.s 60 years ago! Thus you'd need to create a street table as well as a cityStreet table due to the many-to-many relationship that exists. As you can see, something like this could definitely get over-normalized in a hurry!

    As for your DB... I'd take a look at two areas that are usually tell-tale signs for over-normalization: "look-up" tables with a single field in it and "many-to-many" tables.

    The "lookup" table scenerio is easier to spot and rectify. If there seem to be too many different values, then there may be too much normalization. For example, if your hospitalAreaType has only "urban" vs. "rural" (like I said, I out of the loop, so forgive me if this isn't what you mean), then having a separate table may be worth it. If on the other hand you have "Large teaching clinic with 2500 doctors, 12 buildings, and a pharmacy", then maybe the entries are too specific for the record and it may be better to just keep it in one table. One thing you may also consider (although I believe that for Access this may not be as important): sometimes a single character abbreviation may be smaller in size than a numerical value used in a foreign key. For example, "S", "M", and "L" are fairly meaningful in a shirtSize field and may take up less room than an integer equivalent.

    The many-to-many is a little more difficult to see if it's over-normalized. But basically your decision strategy should be based by asking: How likely will one record be duplicated in another. For example, you have an employeeLinkedPhone table, which means that each phone may call multiple employees, while at the same time a single employee may be reached by multiple phones. So you may want to ask yourself for each end of the relationship, how likely will it occur. I can definitely see an employee having a office phone, a lab phone, a cell phone, and perhaps a pager number; thus the one-employee-to-many-phones works. But is the other direction likely? If Bert and Brenda in accounting are the only two people that share a phone, then perhaps it'd be better to just have each phone number pointing to a person, and drop the many-to-many linking table (thus there'd only be one phone-number occurring twice in the database: once for Bert and once for Brenda).
    Last edited by Pyth007; 05-17-07 at 19:48.

  4. #4
    Join Date
    May 2007
    Posts
    12
    Wow, thank you both for your detailed response. This database is for our local hospital and area only. You gave me a lot of confidence for this database. I agree with you about the City / State tables. In thinking about it, the State table is not relevant because every location is in Ohio, so I can just set that field as a default to OH. The zip code I am wanting to populate itself when the city is selected. I see now that I need to revisit that area. Thanks for making me think.

    Pyth, your point about getting "crazy" with normalization is very well made. Thank you. As for the look up tables, there is only one look up table that has a lot of data in it and that is the Title table. This is the title of the emplolyees. Because the titles can get lengthy and will be used for reporting, I need to make sure that they stay consistent. Other than that one, the rest only have roughly 10 records or less.

    The phone table has had me stumped from the vey beginning, especially the employee and department. The Lab department has 15 employees, 10 of them have their own numbers, in some cases they have two different phone numbers (two lines). The direct line for the the Lab department is phone number 6590. The other 5 employees are identified with the phone number 6590 since they do not have a desk with a phone. Therefore in SOME cases it is a M:M relationship. I have no way of knowing how many times throughout the data that this occurs, but I figure I have to be prepared for it. My rationale is I would rather allow for a M:M now for when it occurs versus getting into a bind not being able to assign the same phone number to many people. Make sense? Am I on the right track?

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don' you simply store the phone number(s) against the employee?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2007
    Posts
    348
    This isn't the least bit helpful to you but, what app did you use to draw the diagram?
    It looks alot better than when I have tried to use Visio.

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Regarding phone numbers. I've designed some databases with the "CellPhone", "HomePhone", etc... in the same table as the Address and then I've designed some databases with a separate Phone table with PhoneNumber and PhoneType fields. I put the phone number fields in the Address table for the databases where phone numbers were often returned with the address. I put the phone numbers in a seperate Phonenumbers table where that program called for a lot of multiple phone numbers for an individual (i.e. 2-3 different home type phone numbers, etc... - keeping in mind that 1 individual could own multiple homes and have a lot of different phone numbers for that specific program.) I guess you have to decide as in my opinion it could go either way.

    I also had the addresses stored with the first/last names only to find later that often people had many different addresses and I ended up creating a separate address table. I had a field in the separate address table designating what "type" the address was (i.e. home, 2nd address, rental, billing, etc..) I never separated the street, city state or zip in a separate table. I don't think there's justification for doing this simply because the same street may be repeated.
    Last edited by pkstormy; 05-18-07 at 11:33.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Firebott
    The other 5 employees are identified with the phone number 6590 since they do not have a desk with a phone. Therefore in SOME cases it is a M:M relationship. I have no way of knowing how many times throughout the data that this occurs, but I figure I have to be prepared for it. My rationale is I would rather allow for a M:M now for when it occurs versus getting into a bind not being able to assign the same phone number to many people. Make sense? Am I on the right track?
    no, you are not on the right track

    as george says, just record the phone number against the employee as an attribute

    what if two employees have the same first name? do you then immediately decide to "normalize" the data by putting 'John' and 'Todd' into a FirstNames table, and linking the employee table?

    no, you don't, do you

    i am constantly surprised at the number of people who have the mistaken notion about normalization that it is intended to remove duplication

    normalization often does remove duplication, but that is a merely beneficial side effect

    normalization is not about removing duplication

    if it were, you'd never be able to store the number 4 more than once, and that's just plain silly

    instead, normalization has to do with analyzing the dependency of non-key attributes on the primary key (or portions of it)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    About phonenumbers,

    My inclination would be to have a departments address book, and have this as a sort of seperae entity, and beable to list 1 or100 extension numbers per department, eg, the path lab. (i have already done this for work)

    with regards to employee data, what i woulddo then is have a ouple f extra fields. (department, [linked to depts tabe],extension number, and bleep number)

    Where an employee had no bleep or phone they would just be under the department's extension, if they were say the Consultant for Elderly Medicine, s/he would have thier own ext, and say the Registrar Medicine, would have a bleep and ext.

    This lists a lot of future possibilities too.

    you coud run a query, and print report, showing each department, (and the dept extesnions in the department header) with the staff listed below and thier individual exts/bleeps eg:

    Peaditatrics:
    Special care Baby Unit exts, 6558, 6559, 6560
    Consultant Neontal Care
    Mr. John Smith
    - Ext. 5500
    - Secretary. 5501
    Registrar
    Dr. Francesca Alans
    - Bleep 974


    hope this helps

    dan
    sometimes simple is best.... and i'm just a simple fellow.

  10. #10
    Join Date
    May 2007
    Posts
    12
    Starkmann, I downloaded a free 30 day trial of this application. I really like it. I am submitting a purchase order to my boss. The website is http://www.datanamic.com It is easy to use and you can produce a database directly from your data model.

    PKstormy, I agree with you. Although in this case the users want the zip code to autopopulate when the city is selected. Therefore I had to create a M:1 relationship. I hope I am on the right track with this thought since I have never had to autopopulate a fielf before. Thanks for the ideas.

    R937, I might not FULLY understand normalization, but I feel that I at least have a grasp on it. If I didn't I would not be in the job that I am, creating databases for a hospital. I do realize that normalization is not functioned to remove duplicate data. I can't apply the phone number to the employee because some employees have multiple extentions and multiple phone numbers. If I did it this way I would have [phonenumber1], [phonenumber2], [phonenumber3], etc. fields. From what I have learned from normalization (and no I do not think I am an expert, but merely trying to muddle my way through) is that if you have several fields in one table that have the same data type then you need to combined those fields into one in its own separate table.

    what if two employees have the same first name? do you then immediately decide to "normalize" the data by putting 'John' and 'Todd' into a FirstNames table, and linking the employee table?
    I also must state, for this type of forum, I feel that this quote is a bit trivial.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Firebott
    If I did it this way I would have [phonenumber1], [phonenumber2], [phonenumber3], etc. fields. From what I have learned from normalization (and no I do not think I am an expert, but merely trying to muddle my way through) is that if you have several fields in one table that have the same data type then you need to combined those fields into one in its own separate table.
    absolutely right, that is 1NF (First Normal Form) -- but note, there is no duplication, just a change in structure

    regarding your "a bit trivial" comment, i will take that as a compliment

    coming up with simple examples in the realm of normalization is often a difficult challenge, and i am happy that i have achieved that aim in this particular instance

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2006
    Location
    Huddersfield, UK
    Posts
    154
    This all seems fairly stright forward.

    I wouldn't quite go the extremes of having a table for firstnames and surnames, as it would be a pain in the ass. I'm sure people can manage to type 'John' or 'Alison' in the names box.

    With the phone numbers, yes, have these as a sub-able of your employees. I have , as mentioned previoulsy, done this sort of thing or work, and have attached the address book i designed for them. - feel free to poke around at it and see what i have done.

    dan
    Attached Files Attached Files
    sometimes simple is best.... and i'm just a simple fellow.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok, I know we've all had our little say on phone numbers, but I want to extend my view.
    Why would an employee need more than 2 phone numbers?
    Employees(ID, FirstName, LastName, Mobile, Extension, department)
    George
    Home | Blog

  14. #14
    Join Date
    Feb 2007
    Posts
    348
    Quote Originally Posted by Firebott
    Starkmann, I downloaded a free 30 day trial of this application. I really like it. I am submitting a purchase order to my boss. The website is http://www.datanamic.com It is easy to use and you can produce a database directly from your data model.
    Thanks for the info.

  15. #15
    Join Date
    May 2007
    Posts
    12
    George, some employees have more than one phone number because one is a direct dial line and one that leads to a secretary, for example the CEO of the hospital or one that leads to the department front desk and one that leads direct to their desk. Those are just a few of the examples. The end user (the switchboard operators) want to have the ability to add more than one hospital related number to an employee. I am sure there are not many examples throughout the hospital as the ones I described above. But I was told early on that when creating a database, the end user is the customer, therefore you need to create the database based off their needs. Thanks for your input.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •