Nomalisation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please help, I am very new to normalisation. Can any one of you please go
through my first normalised form am trying to do and see where am going
wrong. Below is my unnormalised form and First normalised form (1NF).
Thank you
jar.


Unnormalised
Training Needs visit worksheet

Department Visited
Contact Visited
Contact Tel
Contact Address
Date of Visit
Time of Visit
Training Consultant Name
Training Consultant phone number
Training Consultant eMail address
Visit duration
Visit Outcome
Further Actions
Training Plan


Training Plan worksheet
Course Title
Date of Training
Time of Training
Training Duration
Training Type
Trainer Name(s)
Trainer Tel number(s)
Trainer eMail address(')
Number of staff to be trainined
Location
Feedback
Course Fee (for group)
Invoiced?
Paid?


Company Name
Address
Town
Postcode
Tel number
Number of Employees
Company Type



























1NF

Training Consultant Name(PK)
Department Visited
Contact Visited
Contact Tel
Contact Address
Date of Visit
Time of Visit
Training Consultant phone number
Training Consultant eMail address
Visit duration
Visit Outcome
Further Actions




Training Consultant Name(PK)
Course Title(PK)
Training Plan
Date of Training
Time of Training
Training Duration
Training Type
Trainer Name(s)
Trainer Tel number(s)
Trainer eMail address(')




Training Consultant Name(PK)
Company Name(PK)
Address
Town
Postcode
Tel number
Number of Employees
Company Type
Number of staff to be trainined
Location
Feedback
Course Fee (for group)
Invoiced?
Paid?
 
If you're doing this for a class, my comments here may not help much
(and I haven't looked at your field names closely; somebody else may be
able to do that). But if you're trying to put together a good design
for your database, I have a couple of suggestions.

In general, the purpose of normalization (1st normal form, 2nd normal
form, etc.) is to remove redundant information from your tables. You
don't need to store data that you could easily compute, such as both a
full name and a surname. (Surname is part of the full name.)

Normally, normalization is a good idea. But it is occasionally possible
to overdo it, as when the Query that computes a field that you might
have stored takes an inordinate amount of time. Microsoft suggests that
if you have to go through many (half a dozen?) linked Queries to get the
value, and your database is slow, maybe storing the value in an extra
field in your Table would work better in your case, even though it would
be redundant and would thus violate normalization rules. But the best
design for you would also depend on whether the Table is used mainly for
lookups, or if the values that the field depends on are frequently updated.

Have you tried using Access's "Table Analyzer Wizard" to help you
normalize your Tables in this database?

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
Please help, I am very new to normalisation. Can any one of you please go
through my first normalised form am trying to do and see where am going
wrong. Below is my unnormalised form and First normalised form (1NF).
Thank you
jar.


Unnormalised
Training Needs visit worksheet

Department Visited
Contact Visited
Contact Tel
Contact Address
Date of Visit
Time of Visit
Training Consultant Name
Training Consultant phone number
Training Consultant eMail address
Visit duration
Visit Outcome
Further Actions
Training Plan
Click to expand...

[...]
 
Hi dude. It's important to note here that normalisation is classified in 5
different forms/categories [or more]--dependent upon the idea of reducing
redundant data, and your needs.

That said it looks like your 1NF is centric for "training consultant name".
You've included this field in every table as the primary key. I'm not sure at
the moment if this is suitable in Normalisation Form 1 (rules applicable to
database architecture). What I'm sure of is: For relational databasing your
result is a 1-to-1 relationship between each of your tables amongst all of
your tables. Additionally you've included many unique identifiers in each
table. Not necessary, except to overcome your 1-to-1 relationship issue [the
idea being: you want 1-to-many relationships]. If this is intentional, hence
1NF, then it looks good.
 
Jason said:
Hi dude. It's important to note here that normalisation is classified in 5
different forms/categories [or more]--dependent upon the idea of reducing
redundant data, and your needs.
Click to expand...

Good point. In a previous message, I had not had time to look carefully
at the field names (and make guesses about what the actual underlying
data are intended to mean), but I would almost certainly not normalize
the tables this way.
That said it looks like your 1NF is centric for "training consultant name".
You've included this field in every table as the primary key. I'm not sure at
the moment if this is suitable in Normalisation Form 1 (rules applicable to
database architecture). What I'm sure of is: For relational databasing your
result is a 1-to-1 relationship between each of your tables amongst all of
your tables. Additionally you've included many unique identifiers in each
table. Not necessary, except to overcome your 1-to-1 relationship issue [the
idea being: you want 1-to-many relationships]. If this is intentional, hence
1NF, then it looks good.


:

Please help, I am very new to normalisation. Can any one of you please go
through my first normalised form am trying to do and see where am going
wrong. Below is my unnormalised form and First normalised form (1NF).
Thank you
jar.
Click to expand...
[...]
1NF

Training Consultant Name(PK)
Department Visited
Contact Visited
Contact Tel
Contact Address
Date of Visit
Time of Visit
Training Consultant phone number
Training Consultant eMail address
Visit duration
Visit Outcome
Further Actions
Click to expand...
Click to expand...

This kind of primary key (besides being considerably longer, I expect,
than the 4-byte key fields Access usually uses) allows your Training
Consultant to visit only one department and talk to one contact. I see
no need for [Further Actions] if you'll always keep track of only one
record. Incidentally, by using a Name as a Primary Key (which must be
unique), you'll have to somehow make sure that you never have two
consultants named Mary Jones. They'll get a lot of each other's mail.
I suggest assigning unique identifiers (such as an "Autonumber"
long-integer field) to each one, and use that as the primary or foreign
key whenever you need to refer to a specific Training Consultant.

Notice that the Primary Key here consists of 2 fields (each comparison
Access makes will take slightly longer than with 1 PK field, and each
field is probably longer than 4 bytes, or are you using short, unique
nicknames?). I assume each record describes a class scheduling meeting
of some [Course Title] made by some [Training Consultant Name], and that
the training consultant will never schedule another class, or at least
that you'll never remember any but the current one. Looks as if you'll
run through lots of consultants that way (or not teach much).


Now, this one connects a Training Consultant with a Company. With
fields like [Number of Employees], it looks as if you'll never want to
have another Training Consultant talk to anyone in that Company.
(Otherwise, you'd have to duplicate much of the information about the
Company in another record, a clear violation of anti-redundancy rules.)

I once worked for a company in which some of the databases were being
designed by people with a good academic (= mathematical) knowledge of
how normalization worked; they were fully conversant with the difference
between 2NF and 3NF, for example. But, even though there is no logical
difference between a random Autonumber field and a collection of 5
fields that jointly form a unique key, there could be a heavy
performance penalty incurred if you do the latter in a real database.
They also didn't believe in commenting the fields, and of course Access
doesn't care what the comments say, as it works perfectly well without
them. But maintaining a database with no comments can be a big
headache, even if you were the original author (3 years ago).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
I'm new to MS Access also, but find these helpful:

1. Table Fields in smallest useable form. [Last Name] [First Name] instead of
just [Name]
2. No repeating fields.
3. Use Primary Keys for each table if possible.
4. Non key fields do not depend upon other non key fields/Every non key field
depends on full primary key (if multi field primary key)
5. No field can be calculated using other data in the database.
6. Every table contains information relating to one subject.

-Charles-
 
Back
Top