Normal Form

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

Guest

I'm trying to get my Database to at least 3NF.

How important is this to someone who is relatively new to Normalization?

Also, I don't quite get what is meant when each table is supposed to
describe the real world? Could someone explain this for me please?

Does 3NF mean that I cannot for example have too many fields in a table? EG:

Employee Name, Call Date, Call Time, Manager, QuestionAnswer (x32), NTLogon
(Of person who enters Data)

I can see how this can be split into tables, but it still gets confusing
with the terminology people use on this newsgroup! I need a dummies guide!

I have a document, "Fundamentals of Relational Database Design" which is
proviing to be quite a help though!

Thanks for your advice,

Toby
 
Let's take a look at your example (Note: there IS more than one way to
reach a 3NF, it depends on what restrictions exist within the data being
modeled).

"Does 3NF mean that I cannot for example have too many fields in a table?
EG:"

3NF tries to build 'tall, skinny' tables --> lots of rows not many columns
So 3NF says you CAN (do) have too many fields in a table (basically when
you look at your table and see 'blank' fields, you ain't at 3NF.

"> Also, I don't quite get what is meant when each table is supposed to
describe the real world? Could someone explain this for me please?"
Click to expand...

You have the following 'real world entities'

Each of these might become a table (depending on data restricions)

Employees
Calls
Managers
ManagedGroup e.g. Departments (implied since we have 'managers', we must
have something for them to manage)

-----------------------------don't really understand these so will ignore
them (they just complicate things anyway)
these could be in the tblEmployee if they are 1:1 (each Employee has one
and only one NTLogon and one and only one QuestionAnswer)
NTLogon ?? QuestionAnswer ??
--------------------------------------------------------------

Have to guess here but expect the following business rules: (different rules
can lead to different data models)

Employee is assigned to one and only one ManagedGroup (frequently not the
case in modern organizations, but then we would have to go a Many:Many
relationship. I don't want to go there for simplicity sake right now ;>.
Managers manage one and only one ManagedGroup --again may or may not be true
in a modern organization.
Each ManagedGroup has one and only one 'manager'
Employees make/take many calls ( I will assume Employees 'take calls')
each call has 1 and only one "caller" and/or CallTaker.
-----------------------------------------------------------------------
So we will need the following tables

tblEmployee
EmployeeID (primary Key)
EmployeeLastName (text)
EmployeeFirstName (text)
other stuff that relates to the employee (Not to employment)
tblManagedGroup
ManagedGroupID (Primary Key)
Manager_EmployeeID (Foreign Key) --> tblEmployee (all managers are by
definition employees)
ManagedGroupDescription (text) (some human meaningful words, like
'Marketing', "Sales", etc.
other stuff relating directly to the managed group

tblEmployeesAssignedToManagedGroup
---------------------------------------------------------here you have two
choices 1 combine the ManagedGroupID/EmployeeID into a unique Primary key or
add a Unique primary key (autoinc)

ManagedGroupID
EmployeeID
------------------>Combined
unique primary key based on ManagedGroupID and EmployeeID -- this will make
sure that a employee can be assigned to a ManagedGroup once and only once.
And using cascading deletes will allow access to delete the relationship
when you fire Jake the Stupid -- all his assignments to ManagedGroups will
be gone.

or
------------------

EmployeeManagedGroupID (primary key)
ManagedGroupID (foreign key ) links to the tblManagedGroup
EmployeeID (foreign key) links to tblEmployeeID
Now if you want to limit employee to group only once build another
unique key based on managedgroup and employee.

-----------------------------------------------------------------------------

tblCalls
CallID (PrimaryKey)
CallDateTime --dateTime
EmployeeID (Foreign Key) --M:1
CallingData (this could relate to a customer table through a
customerID or could be the calling telephone number or ... what ever you
need)
CallingNotes
other stuff relating to the specific call.
-------------------------------------------------------------------------------------

The above is just a start. As you can see there is a dependence on knowing
enough about the buisness and its 'rules of organization' to be able to
'properly' model the database. Then of course, as soon as everyone says
these rules are 'iron-clad' the first exception pops up, so try to not
depend too much on the 'rules'. For example I will assure you that as soon
as you build based on "Each Employee will be assigned to one and only one
ManagedGroup". Someone will be assigned to two or more. You will have
managers that are assigned to manage more than one ManagedGroup.

Hope this helps more than confuses the issue.

Ed Warren.
 
Hi Ed, that's great! Thanks for the response!

There are more than 1 managers for some groups, And they might change in the
future, so I have to make sure that if they change campaigns then it doesnt
mess my table up!

I can see a Managers Table with a Campaigns foreign key in it to say what
campaigns they manage, So if this is linked to a Campaigns table I presume
that it is a many-many relationship?!

Regards,

Toby
 
Right, so now I have these relationships on a piece of paper:

Mangers do the Monitoring sessions for employees, An Employee can only have
one manager monitoring them, therefore we have a 1M Relationship between
Managers and Employees.

Employees Belong to Departments, They can work on different departments if
they are cross trained, so I am looking at an MM relationship.

Managers Manage Departments, they can Manage more than one, and each
department can have more than one manager, therefore we have an MM
relationship.

Employees work for Employment Agencies, they can only be contracted here
through one agency, but an agency may be contracting more than one employee
at our company, therefore we have Employees 1 => M Agencies.

Agencies and Departments have no relationship whatsoever.

Employees make calls, one call can only have one employee on it, therefore
we have a 1M relationship between employees and calls, since employees make
more than one call usually.

In these Calls, Criteria is assesed by the Manager, there is more than 1
criteria per call, and all calls have Criteria, therefore we have an MM
between Calls and Criteria.

These calls are addressed to Customers, an Agent can call many Customers,
and many Agents can call the same customer, therefore we have an MM
relationship between agents and customers.

Is this right? =P
 
Already my head spins.

As you have now found out there ain't any 'simple' data models. You appear
to be on the right track. My advice is to really 'work out' the details,
like you have and get the 'design right' from the start, otherwise you will
face hours of frustration later trying to 'untangle' the mess.

I will have to defer to someone else with the specifics, but now you have
posted enough for one of the 'experts' to buy in and provide additional
information.

Looks like we have a couple of 'lurking entities (classes)'.

Monitoring Sessions (date/time, employee, manager, results, etc)
perhaps the employee is monitored by different managers during different
monitoring sessions?
Then the relationship is not employee to manager but
employee -->m:1--> monitoring session 1:m<--Manager

or the employee is ALWAYS monitored by the same manager then:
employee M<-->1 Manager

Employement Agencies

Departments

Maybe there is enough specifics now, one of the EXPERTS on the list can
help.


Keep working at it, it takes several iterations through the loop to get even
close.

Ed Warren.
 
Thanks very much, that's great! I don't have internet access at work at the
moment, but when it's put on I will upload a relationship diagram, everything
is working as expected (My tables use ID numbers as references, so I am using
queries to see the 'Plain English' data!)

I think it might even be BCNF!
 
Back
Top