Problem with restructuring an old database

Vyc · #1 (**permalink**) 08-09-07, 05:51

Hi all,
I recently began a task on converting an old (ancient?) Foxpro 2.6 database and a program built with it to manage the database into a more modern, web-based database application. After some interesting difficulties I have managed to produce an exact conversion of the dbc database into MySQL database.

The current problem is modernizing the structure of the database. As it is now, there are three tables relevant to the problem: Customers, Products and Orders. Originally, none of the tables contained any form of primary or foreign keys so a lot of the data was duplicated and there were numerous temporal tables.

As I converted the tables to MySQL, they now have primary keys assigned to them. What I’d like to do is to have the order table like this: orderID, customer ID and productID. What I have right now is orderID, customerName, customerID (empty) and productID. The customerID column is currently empty in all the records of the Orders table. There are over 300 000 records. Is there a way to automatically fill in the missing customerIDs in the Orders table, using customerName column as the identifying factor? I’d really appreciate an automatic solution so that I wouldn’t need to update all 300 000 records manually..

I would have had the exact same problem with the productID and the Orders table but the table happened to include a mostly decorative identification value for each product which was also found in the Products table so I took a shortcut and converted that value in to the current productID, solving that problem. In this case, no such interlinking value exists, except the customerName value itself.

Any help is greatly appreciated. I’m using version 5.0.41 version of MySQL.

gvee · #2 (**permalink**) 08-09-07, 07:11

Quote:

Originally Posted by Vyc

What I’d like to do is to have the order table like this: orderID, customer ID and productID

Why? There is no need for this.
ONE Customer can have MANY Orders

Customers(CustomerID, Surname, Forename, Address, etc)
Orders(OrderID, CustomerID, etc)

Vyc · #3 (**permalink**) 08-09-07, 08:01

Quote:

Quote:
Originally Posted by Vyc
What I’d like to do is to have the order table like this: orderID, customer ID and productID
Why? There is no need for this.
ONE Customer can have MANY Orders

Customers(CustomerID, Surname, Forename, Address, etc)
Orders(OrderID, CustomerID, etc)

First, thank you for responding.

Hm. Maybe I failed to correctly explain the problem. You see, the way you described the tables is exactly what I'm aiming for. Let me see if I can explain it better this time. It is an old Foxpro 2.6 database converted into MySQL database, filled with hundreds of thousands of records.

Order table was originally structured like this: Customer name, Product name, Product serial, etc. No primary or foreign keys. Primary key OrderID was added in the conversion process. I have modified the table so that it now has OrderID, CustomerID, CustomerName and ProductID (former product serial). Of course, at this point the CustomerID column does not contain any data.

I'm trying to find out a way to automatically fill in the missing CustomerIDs, possibly by using the customer name as the linking factor. After the missing CustomerIDs have been recorded, I would drop the CustomerName column so that the final table structure would be as Orders(OrderID, CustomerID, ProductID, etc,) just as you described. Essentially, I'm just trying to avoid having to manually edit all the 300 000 records in the Orders table to include the corresponding CustomerID.

Thanks.

gvee · #4 (**permalink**) 08-09-07, 08:08

First off, I'd write a query that shows Order records with missing CustomerID's. Then I'd use an INNER JOIN to join the Customers table ON CustomerName. If you can produce this, then let me know

aschk · #5 (**permalink**) 08-10-07, 06:04

Surely all you want is the following :

Code:

UPDATE Orders,Customers 
SET Orders.CustomerID = Customers.CustomerID 
WHERE Orders.CustomerName = Customers.CustomerName

aschk · #6 (**permalink**) 08-10-07, 06:09

Quote:

Why? There is no need for this.
ONE Customer can have MANY Orders

Incidently the issue is not with the one->many relationship, it's with the data integrity...
e.g. customer suddenly decides one day to head on down to the town registry and change their name to 'Alfred Hitchcock', and now they want their database entry to reflect that.
Well, on they hop to the interface, and go ahead and alter their details (which relates to the Customers table), and WHOOPS BANG! All their orders suddenly don't appear to exist. Would this be because the tables no longer join up?

aschk · #7 (**permalink**) 08-10-07, 06:13

Also, after you have run the query I have provided you can look up any customers who don't have any orders (i.e. it's possible that their names don't match):

Code:

SELECT CustomerID,CustomerName 
FROM Customers
WHERE CustomerName NOT IN (
SELECT CustomerName FROM Orders GROUP BY CustomerName
)

Vyc · #8 (**permalink**) 08-10-07, 06:19

Thank you both for your replies, I finally arrived to aschk's solution while going through a query that pulled the customer ids from the customer tables, everything else from the order tables and inner joined on customer names.
It seems that my (my)SQL knowledge was quite bit rustier than I thought.

Oh well, I still have to sort out some double entries in the customer table and some customers in the orders table that do not exist in the customer table, not even mentioning rest of the tables so at least I have something to practice on

Thanks again.