Access noob - need help Normalizing a database to 3NF, did I do it rite?

[oZone]

I'm still learning Access, studying normalization now and need some help figuring out if I've successfully converted an un-normalized database into 3NF.

This is the un-normalized table:
* tblOriginal (ClientNumber, ClientName, ContactName1, ContacPhone1, ContactName2, ContactPhone2, ContactName3, ContactPhone3, ClientType, Billed, Paid, TechNumber, TechName, TechLevel, TechRate)

This is what I came up with in my first 3NF attempt:
* tblClient (ClientNumber, ClientName, ClientType, Billed, Paid, TechNumber)
* tblClientContact (ClientNumber, ContactLastName, ContactFirstName, ContactPhone)
* tblTech (TechNumber, TechFirstName, TechLastName, TechLevel)
* tblTechRate (TechLevel, TechRate)

I've attached the database below. It has the newly created tables, as well as the original un-normalized table.

Could someone please let me know if I did it rite, and if not, provide some advice to help me get it in 3NF?

Many thanks

 

[Rabbie]

Please don't double post. It just irritates the people who may be able to help you

 

[oZone]

I wasn't sure which forum subject to post in, my apologies. I thought (after my original post) that this would be the better forum to post in, but dont know how to delete my original thread...

 

[Rabbie]

I wasn't sure which forum subject to post in, my apologies. I thought (after my original post) that this would be the better forum to post in, but dont know how to delete my original thread...

To delete a thread that you started just click the Edit button. Then click the Delete button

 

[oZone]

Okay, thanks. Will delete original post now.

 

[oZone]

Sorry, forgot to attach the database in question earlier. Here it is.

 

[John Big Booty]

I was looking at the other thread There where already a couple of good responses there

Any way here's another link on the topic of Normalization

In the majority of cases I would use an Autonumber as a Primary Key (PK), rather than a text string.

Any field in a table that starts showing a pattern of repetition I would hive off into it's own table with it's own PK, ideally you would identify this during the design of your tables, and before you start inputting data.

I've had a play with your Sample DB and made a few changes.

 

[WayPay]

Table tblClientContact does not have a unique key.

 

[oZone]

In the majority of cases I would use an Autonumber as a Primary Key (PK), rather than a text string.

Can you explain why (cuz i'm still a noob lol)?

I've had a play with your Sample DB and made a few changes.

Would this be considered an example of 3NF? Or would the example you provided be higher than 3NF?

I'm just trying to get a clear understanding of how this table would look when in 3NF form.

Table tblClientContact does not have a unique key.

Yes, I was unsure how to handle the tblClientContact table... After adding the contacts to it I realized that I couldnt use the client number for a PK because obviously it would add redundant entries in a PK which I know is NOT acceptable... so I got stumped... lol.

What would you do in that case? How would you solve that issue?

 

[John Big Booty]

Can you explain why (cuz i'm still a noob lol)?

It's easier to control and takes up less storage space.

Would this be considered an example of 3NF? Or would the example you provided be higher than 3NF?

I'm just trying to get a clear understanding of how this table would look when in 3NF form.

The criteria for third normal form (3NF) are:

* The table must be in 2NF.
* Every non-key attribute must be non-transitively dependent on the primary key.

All attributes must rely only on the primary key. So, if a database has a table with columns Student ID, Student, Company, and Company Phone Number, it is not in 3NF. This is because the Phone number relies on the Company. So, for it to be in 3NF, there must be a second table with Company and Company Phone Number columns; the Phone Number column in the first table would be removed.

See also for more on 3NF

 

[WayPay]

Can you explain why (cuz i'm still a noob lol)?

In short: they take less storage space and work faster when JOINing on them.

Would this be considered an example of 3NF?

Normalization theory does not concern itself about the actual implementation of the schema; it says nothing about the data itself, only about its organization. The table needs an extra field to make each row unique: ContactNumber (for instance). The primary key should then be (ClientNumber, ContactNumber).

About BigBooty's modifications:
I agree in practice with the TBL_ClientType table, but there's no ClientTypeDescription in the original table. If it's about 3NF, the ClientType field alone is not a repeating group, so there's no real need for a separate table. The TBL_ServiceCalls is a huge step towards the implementation of a useful system, but its existance can't be inferred from the original table.

The AutoNumber PK fields: it's very common to implement primary keys like this (although the original text-keys should be set up as alternate keys with a unique index); for the purposes of 3NF there's no need for these extra fields.

In practice, theory is often not practiced.

 

[John Big Booty]

The reason I have split the Billed, Paid and Tech fields into TBL_ServiceCalls is, that this is data that has nothing to do with the client details, as such. It will presumably be ongoing, and will likely represent any number of service calls going forward into the future. However once you start tracking billing and payments, it seems to me that you are straying into the area of accounting, which has it own peculiarities, conventions and requirements, and for which there are any number of the shelf products. Why reinvent the wheel?

 

[oZone]

I really appreciate you guys taking the time to help me learn more about normalization.

I understand that the table, if it were to be used in a real-life situation, would need the modifications BigBooty described.

My focus, however, is not on the longevity of the database, nor on its potential for growth. My focus is to use it as it is, and apply the minimum amount of alterations needed to get it just to 3NF in order to better understand the concept. It is, after all, a fictional database I created in order to teach myself the concept of normalization.

I need to go study out the advice given here. I'm sure I'll be back to post additional questions.

 

[oZone]

I have another question;
Is there any way of converting the original, un-normalized table (see first post for DB attachment) into Third Normal Form without adding any additional fields to the resulting tables?

Or is it absolutely necessary to add additional fields to achieve 3NF?

 

[Rabbie]

I have another question;
Is there any way of converting the original, un-normalized table (see first post for DB attachment) into Third Normal Form without adding any additional fields to the resulting tables?

Or is it absolutely necessary to add additional fields to achieve 3NF?

As the normalization process involves splitting the existing table into two or more tables you will have to add a field in the Child table to hold the Foreign Key from the parent record.

 

[oZone]

Okay, thanks Rabbie.

Another question; For a table to be in 3NF, does every table in the DB have to have a primary or composite key? I should know this, but for some reason my brain isnt working at the moment...

My question arises from wondering if I can simply join the Clients and ClientContacts tables using the ClientNumber field (PK) in the Clients table, and the ClientNumber field (FK) in the ClientContacts table, without having to define a composite key in the ClientContacts table.
I say "composite key" because if you look in my posted example DB the ClientNumber field in the ClientContacts table has duplicate customer numbers due to several customers having more than one contact...


Example:

Can I do this:
tblClients (ClientNumber, ClientName, ClientType, Billed, Paid, TechNumber)
tblClientContacts (ClientNumber, ContactLastName, ContactFirstName, ContactPhone)

Instead of this:
tblClients (ClientNumber, ClientName, ClientType, Billed, Paid, TechNumber)
tblClientContacts (ClientNumber, ContactLastName, ContactFirstName, ContactPhone)

Or would a composite key, in this case, be acceptable in 3NF?

 

[John Big Booty]

I would do

tblClients (ClientNumber, ClientName, ClientType)
tblClientContacts (ContactID, ClientNumber, ContactLastName, ContactFirstName, ContactPhone)
tblClientServiceCalls (CalID,ClientNumber, Billed, Paid, TechNumber)

To avoid a situation where you have two people at the same company with the same last name.

Also I would remove Billed, Paid, TechNumber as it not relevant to the rest of tblClients as it appears to be billing for services calls and by storing it in tblClients you are limiting your ability to track the history of said services calls.

 

[oZone]

Awesome BigBooty, thanks a ton! That helps clarify a few things.

I'm curious how you (or anyone) would handle this next situation;

I have the two following tables:
tblTechs (TechNumber, LastName, FirstName, TechLevel)
tblTechRates (TechLevel, TechRate)

The billing rates for all Techs may change in the future. In other words, you'll never have one Master Tech charging $35 and another charging $42, for example. If the rate for a Master Tech changed to $60 an hour, then all Master Techs will charge $60/hr.

Would the table I described above be a realistic way of accomplishing this without having to add additional fields? Or how would you handle this?

 

[John Big Booty]

Glad to have helped

......... It is, after all, a fictional database I created in order to teach myself the concept of normalization.

.........

Instead of working on a fictional DB, to try and get to grips with normalization. Why not create a DB to catalogue your CD and/or DVD collection (for example)? that way not only would you learn the theory of DB design, but also come to grips with the practical side of your DB.

 

[oZone]

Good suggestion, I may do that.

I have a friend that is a DB designer and is quizzing me on this DB I made. He's asking me to solve certain problems to help me learn. But he will not give me the answers lol! He wont even answer my questions, so I have search the web for nice places like this and ask you guys...

Thats why the last question I asked (about the Tech rates) is more specific than the others have been.

How would you handle that situation?