Re: Table Normalization

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: '69 Camaro (ForwardZERO_SPAM.To.69Camaro_at_Spameater.orgZERO_SPAM)
Date: 12/12/04 

Date: Sat, 11 Dec 2004 18:30:32 -0800

Hi, Jonathan.

> Any suggestions would be greatly appreciated.

You may want to look even further ahead in your organization's needs for
this database, such as how many other types of phone numbers are likely to
be added. Pagers come to mind.

I suggest taking these five steps:

1.) Create a table for the types of phone numbers. Add an Autonumber
primary key and the "PhoneType" field to hold the name of the phone type.
It might look like this:

Table: tblPhoneTypes

PTID PhoneType
1 Business
2 Cell
3 Home
4 Pager

2.) Create a query that gathers the information from the EmpInfo table in a
normalized structure and name this query qryEmpPhones:

SELECT EmpNum, HomePhone AS PhoneNum, 3 AS PTID
FROM EmpInfo
UNION
SELECT EmpNum, CellPhone, 2 AS PTID
FROM EmpInfo
UNION SELECT EmpNum, BusinessPhone, 1 AS PTID
FROM EmpInfo;

3.) Create a make table query with the following SQL statement:

SELECT * INTO tblEmpPhones
FROM qryEmpPhones
ORDER BY EmpNum, 3;

4.) Open the tblEmpPhones table and edit the field defaults (such as size
of the text field), assign the primary key, open the table properties and
change the Subdatasheet Name combo box to [None], then save the new table.

5.) Open the Relationships window and create the relationship between the
tblEmpPhones and tblPhoneTypes tables, then save the change to the
Relationships window.

I'd also suggest not using special keys, like # and spaces in field names
and table names to avoid bugs later. You may even want to add an additional
field to the tblEmpPhones table to indicate which phone number is the
primary phone number to reach the employee at.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

"Jonathan Brown" <Jonathan Brown@discussions.microsoft.com> wrote in message
news:E153D5DD-F59B-4879-B546-A77F67E242FA@microsoft.com...
> I didn't normalize my database as well as I wish I had. I have a table
> called EmpInfo that's built essentially as follows:
>
> Emp#
> HomePhone
> CellPhone
> BusinessPhone
>
> What I would likek to do I build a different table called EmpPhones that
> would like like this:
>
> Emp# (this field would be related to the Emp# field in table EmpInfo)
> Phone#
> Type (the type field would be a lookup to another table with a list of
> different types of phone number)
>
> The Emp# and Phone# fields together would make up my multiple field
primary
> key.
>
> How would I combine the the HomePhone, Cellphone, and BusinessPhone into
one
> column, and then have 2 other columns with it's associated Emp# and Type?
>
> Any suggestions would be greatly appreciated.
>

Relevant Pages

  • Table Normalization
    ... I didn't normalize my database as well as I wish I had. ... called EmpInfo that's built essentially as follows: ... Emp# (this field would be related to the Emp# field in table EmpInfo) ...
    (microsoft.public.access.tablesdbdesign)
  • Re: to optimize a select join
    ... My preferred garden variety of databases has a syntax extension ... Rows form EMP? ... Every database has its own syntax for outer joins. ... To make long story short, I hate ANSI joins, with passion. ...
    (comp.lang.php)
  • Re: Auto fill of related fields in a form
    ... For some reason my sample Northwind ... Check this sample database which should be installed with your Access ... name and corresponding emp. ... and then have the related employee ...
    (microsoft.public.access.forms)
  • Re: Problem with a simple CMP bean while working on JBoss
    ... > Problem with a simple CMP bean while working on JBoss ... > Database: MS SQL SERVER ... > Table Name is: emp ...
    (comp.lang.java.programmer)
  • Problem with a simple CMP bean while working on JBoss
    ... Problem with a simple CMP bean while working on JBoss ... Database: MS SQL SERVER ... Table Name is: emp ...
    (comp.lang.java.help)