Normalization

[jlt199]

Hi,



I've seen on here some case studied of non-trivial database designs implementing normalization, however, for the life of me I can't find them again. Is anyone able to point me towards some?

I've been through several youtube and pdf tutorials but I'm obviously not understanding the theory well enough to apply in a non-trival design.

Many thanks

[orange]

Principles of Relational Database (short and very good)
http://forums.aspfree.com/attachment...2&d=1201055452

Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials:
These are quite good for learning by Watching/Listening
rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

[ItsMe]

That is funny, I was just about to link to Orange's post from 2009 that has the same youtube videos.

Here is a link to reseved names
http://allenbrowne.com/AppIssueBadWord.html

[jlt199]

Thank you, I think there was only one link I hadn't already worked through.

I've attempted to normalize my database design, working in the same methodical way of the YouTube videos you have kindly linked to - although, when it came to third normal form I may have gone a little crazy. Does it look like I'm at least thinking along the right lines?

DatabaseDesign.zip

Thanks again

[orange]

Do you have a description of your business. It's only knowing WHAT you are dealing with that we can focus comments.

This link is related to customers and orders. http://www.rogersaccesslibrary.com/T...lationship.zip

But there are other examples on this page. There are 4-5 examples.

The best way to learn or "start to understand" normalization is to actually build the model. It can be pencil on paper; then use some test scenarios to see if the model actually meets your needs. If there are any anomalies/issues, reconcile every one --it's either the model, the test scenario or the business rule. Fix whatever it is and test again; and again until it works.

Good luck

Good luck.

[jlt199]

Thank you. This particular database is for our quit smoking project the main features of which are

• The patients can either self-refer of be referred by family doctor. We need to know which though as we can't prescribe meds to self-referrals
• Once referral is received we try contacting patient, currently up to 3 times, to make a booking onto classes (they need to be booked onto a session 1, 2, 3 and 4)
  
  • This is one point where the patient can choose to defer, in which case we will follow up with them in 3 months
  
  
• The day before each class we give the patients a reminder call of the class time and location
• After all classes have finished, we followup with the patient at 1 week, 2 weeks, 4 weeks, 3 months, 6 months and 12 months to see how they are doing and check their current smoking status

[orange]

I looked at your xls material and created a model from it.
I modified a few Fields on junction tables. I did not use compound PKs, I made a single PK and included the PKs from the related tables as separate fields --in some cases I named them with a fk prefix. I guessed at some of the meanings but am attaching a zip of the database skeleton. I have not vetted the model and can not/did not identify a Deferal table, but it is referenced in your material.

You can use it as you see fit. It may be a start.
In junction tables I often use a single PK, and use the PK from the other tables in a composite unique index to prevent duplicates.

Hope it's useful to you.

You mention Sessions but I didn't see it referenced in your xls.
If it's a self referral, where does Family doctor fit?

These are the kinds of questions that will help refine WHAT you business really is --the details.

Good luck.

[jlt199]

Thank you for spending the time to do this for me, it's HUGELY helpful!

I apologize that I have the habit of using class and session interchangably

If you don't mind, I do have a couple of follow-up questions:
1) Why did you choose to link the classes to the patients and not to the referrals?
2) Whats the best method of storing historic data, for example if a patient moves then we need the new address for contact purposes, but we'd also like the historic address so we can analyze where referred patients are coming from? Same with status. You've given me a table that gives the date of a patients status change, and their new status. Would searching this table for the latest status change be enough to determine their current status, or should it be pulled out somewhere else?

Thanks again

[orange]

1) Why did you choose to link the classes to the patients and not to the referrals?
My thinking was Patients take Classes

2) Whats the best method of storing historic data, for example if a patient moves then we need the new address for contact purposes, but we'd also like the historic address so we can analyze where referred patients are coming from?
Add a PatientAddress table PatientId will be foreign key, put a EffectiveDate with the address info. Current address is the one with the Max(effectiveDate)

Same with status. You've given me a table that gives the date of a patients status change, and their new status. Would searching this table for the latest status change be enough to determine their current status, or should it be pulled out somewhere else?
Use the Max(statuschangeDate) to determine the curent/latest status.

Good luck with your project.

[jlt199]

Thank you so much

[orange]

Glad to help.
Good luck with your project.