I have homework where I need to create simple web interface and a DB normalized to 3NF. I've chosen ISP client selfcare as my database purpose. Web interface is no problem, the normalized DB is. After a really hard time I've come up with the model below.

I'd like to know if it's in 3NF?! If no, I'd be glad if you explained why.

Database model

  • What is client.level? – podiluska Sep 19 '12 at 14:29
  • int (0-5) representing access level for the user. Data types are not correct, becouse I needed to know if the structure is right, and later I would fix the data types. – r00m Sep 19 '12 at 14:40

I would say that you are pretty close to be fair - and I think (though it is a personal opinion) that 3NF is more important at an educational level that it is when it comes to the real world. I say this because it is not always logical or practical to obtain perfect 3NF, this could be due to an applications design or the performance of the queries etc etc...

That being said, the best thing I could give you to remember 3NF is a quote a friend once told me (who read it in a book to be fair!) and that is this: Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key. - I think the person who said it originally was Bill Kent.

So on that basis, there are some minor improvements you could make; let's take your city table for example. You have an ID, Title and Post Code; well if you think about it, there are many Post Codes per City, and your design would mean that for each new Post Code you would need to add the ID and Title again - thus breaking the requirements for 3NF. For this you could have an additional table called PostCodes which could contain an ID, Name and City columns, you could then reference your city this way - which is better. The same goes for your Client Info table which has an address field - but it's possible that an address can be shared among many people, as can phone numbers, email addresses etc etc you get the picture!

The best way to remember it is this - if there is a possibility that you have to duplicate information just to satisfy your design - then you either have a special requirement (which is possible - like I said "real world") or your design is wrong - you need to judge it on a case by case basis.

I hope that this helps you.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.