access 2000 help with third normalizing

[TXH0207]

I am new to this and looking for guidance.

I am trying to set up a customer data system via a report that a Technician fills out.

The forms that a technician fills out looks something like this.

Table1 (Customer_ID_Numbers)
Job_Number
Customer_Number (Primary Key)
Record_Number

Table2 (Facility_Information
Facility_ID (Primary Key)
Facility_Name
Address
City
State
Zip
Phone_Number

Table3 (Contacts)
Contact_ID (Primary Key)
Contact
Title

Table4 (Returned_Equipment)
Returned_ID (Primary Key)
Serial
Model
Description
RA

Table5 (Equipment_Sold)
Sold_ID (Primary Key)
Model
Serial
Description
List_Price

Table6 (Total Charges)
Total_ID (Primary Key)
Total_Equip_Charge
Total_Labor_Charge
Total_Less_Tax

My problems are with Tables 3, 4, and 5. Tables 4 and 5 can have over 20 different Model's, Serial's, Descriptions and List_Price's. (List Prices are only in Table5)

Table3 can have several different contacts and titles.

My issue is normalizing it so nothings redundant with all this info per customer.

Thanks in advance

Mike


Mike Haas

 

[formerTexan]

Hello Mike,

Data that is repeatedly referred to and entered is a prie candidate for lookup tables.

Sounds as if you need a lookup table for your equipment, perhaps with fields for a Primary Key, Model, Serial Number and other related information.

THen add a combo box to the form that the technician uses. Bind the combo box to the form's recordset, but set the combo box's rowsource to the lookup table.

This will allow handy reference to the model information, eliminate entry errors by technicians, reduce the size of your Equipment_sold and Equipment_Returned tables (since they will only have the Primary Key from the lookup table rather than full equipemnt information) and keep the maintenance of model information tidy (since it is all in one table).

Cheers,
Bill

 

[lespaul]

Also check out 'The Fundamentals of Relational Database Design'



Leslie

 

[TXH0207]

Thanks formerTexan! for the advice I will see what I can do with your advice.

Thanks again

Thank you lespaul for the website,

Take Care,

Mike

Mike Haas

 

[jaydeebe]

Getting rid of the sold_ID and Returned_ID and replace with equipment_ID and have an equipment table as well. This may go some way to normalizing the design.

Equipment table - Equip_ID, Model, description, list price, Stock Level

Sold Table - Equip_ID, Date,

Returned table - Equip_ID, Date

Jaydeebe BSc (hons)
MCSA 2k
MCSE 2k

 

[bdora]

Yes! By all means dump those 2 tables (sold and returned). Replace them with one table describing all attributes (properties) of the equipment. Add another table to track sales (or call it orders if you like that name). The tbl:Equipment should have additional fields to record the "quantity on hand" and the "reorder quantity". Log all ordres/sales into the orders table and deduct the quantity sold from the quantity on hand. When items are returned (and resalable) increment the quantity on hand value accordingly.

The Equipment table should only house information about the product and should include the purchase price (what YOU paid) and either a fixed or floting profit margin for this item or a flat retail price. Either works.

If you set this up correctly, you can also dump table 6 (Total Charges) since these can easily be determined on the fly.

Hope this helps.

 

[TXH0207]

Thanks for all the input. I am trying to apply all your suggestions. I appreciate all of you helping the rookie out.

Thanks to all,

Mike

Mike Haas