Question on database design.

[polman]

Sorry for the repetition... I 've already posted this to
..access.replication newsgroup...
What is the best way to resolve the following situation:
Let say we have a company with 3 departments. Each of them assign a
unique ID number to its customer. A person may be customer to one
department only or to all of them. If the company decides to give an
end to this mess by assigning a globally unique ID (for all three
depts) what is the best way to do it without miss the old references of
each department to its customer?
My solution seems very simple and I would like your comment on it: Just
start to assign a new ID for the new customers as well as old ones in a
primary key while in other fields stores the old IDs from each
department. That means that there will be three fields for each
department and an additional one for the global ID. So a customer may
have one (if new), two, three, or four (old customer of all
departments) fields. This seems to me very ugly but I can't think
something else. Maybe assigning a composite (of the tree fields)
primary key is better solution.
Could someone help me with this?
Thank you in advance.

 

[Geoff]

Read about normalizing data.

It appears your table has broken the "first normal form" (the first rule for
normalizing data in tables). You have a repeating group - ie three fields
for three departments. Instead, you should have three tables - one table
for customers with a primary key field that uniquely identifies each
customer, a second table for departments with a primary key field that
uniquely identifies each department, and a third table with just two fields
to store the key for the customer and the key for the department. The keys
in this third table are known as foreign keys. You would create a
one-to-many relationship between the customers table and the third table
(between the key and foreign key) and you would create a one-to-many
relationship between the departments table and the third table (between the
key and the foreign key). You could correct your current situation by
creating new tables and running a series of update queries to move the data
from the old to the new tables.

I hope you can figure it out.
Good luck.
Geoff

 

[Guest]

What you have is a many-to-many relationship between Departments and
Customers so you need three tables, Departments, Customers and
DepartmentCustomers, the last modelling the relationship between the other
two by means of two foreign key columns e.g. DepartmentID and CustomerID
referencing the primary keys of the others respectively. The two foreign key
columns make up the composite primary key of the DepartmentCustomers table.

The first thing to do is to create a table of distinct Customer names etc
with a unique primary key e.g. an autonumber. This can be done by appending
the current customer data to a new table in which a unique index has been
created on the relevant columns which identify the customer uniquely (you
might need to do some manual editing of the original data beforehand if, for
instance, values such as customer names which you use for this index have
been enterd slightly differently by each department). The index will prevent
duplicate rows for the same customer being inserted into the table.

Do the same for departments, though this can probably be done manually in
view of the limited number of rows required.

To populate the DepartmentCustomers table join the new Departments and
Customers tables to the original ones on the columns you have used for the
unique indexes in the new tables and join the original tables on the old
customer ID numbers, and append the CustomerID and DepartmentID values from
the new tables to DepartmentCustomers.

Once you are satisfied that the three new tables re correctly populated you
can dispense with the original tables.

Ken Sheridan
Stafford, England

 

[aaron.kempf]

more important than 'first normal form'

dont use MDB; it is obsolete; anyone using MDB for anything in the year
2006 should be fired and spit upon

ALL THREE MAJOR VENDORS HAVE A FREE EXPRESS EDITION
Lose the ****ing training wheels; kids

-Aaron