Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Posts
    3

    Unanswered: Problem with restructuring an old database

    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 Id 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? Id really appreciate an automatic solution so that I wouldnt 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. Im using version 5.0.41 version of MySQL.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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)
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2007
    Posts
    3
    Quote:
    Originally Posted by Vyc
    What Id 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.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Surely all you want is the following :
    Code:
    UPDATE Orders,Customers 
    SET Orders.CustomerID = Customers.CustomerID 
    WHERE Orders.CustomerName = Customers.CustomerName

  6. #6
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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?

  7. #7
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    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
    )

  8. #8
    Join Date
    Aug 2007
    Posts
    3
    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.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Vyc
    Oh well, I still have to sort out some double entries in the customer table ...
    ah, the perils of the surrogate key...


    Quote Originally Posted by Vyc
    ... and some customers in the orders table that do not exist in the customer table
    ah , the perils of the missing foreign key...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •