Converting a Paradox db - normalize vs maintaining work process

[cbnsd]

Sorry for the long post. My basic question is at the end, the middle describes the scenario. Started a new position as a general IT person (db experience = low), was handed a old Paradox db and asked to convert it to Access. Imported the tables, and started replicating the screens/forms with moderate success. The two main tables are essentially a "Customers" and "Orders" relationship.



But got to a point on the "Orders" form where I realized the data wasn't following typical normalization guidelines.
Here's the current logic for entering an order:

1. A Customers form allows for entering typical customer fields.
2. Some order specific fields like SalesAssociate, and OrderDate are also entered in the Customers table via the Customers form. (they have reports showing customers with orders pending from this table).
3. When an order is complete the Orders form allows for entering the Customer#, which brings up the typical customer details from the Customers table (name, address...) as well as those order specific details (SalesAssociate) also from the Customers table.
4. Additional order specific info is also entered on the Orders form, Order# (primary key/autonumber), CompletedDate, OrderAmount, etc.

Up to this point no order has gone into the Orders table (I checked). With Pardox you have to Post the data.
Here's what happens when you Post from the Orders form:5. Dialog box reminds you to update Customer information.
6. New form comes up to modifiy some Customer information, ("Next renewal date"), changes are optional.
7. Goes back to the Order form and asks you to Post again.
When Posting is complete what has happened is:

8. The order specific details (step #2) have been deleted from the Customers table.
9. A new record is added to the Orders table with a unique Order# and the Customer# from the Customers table.
10. The order specific fields deleted in step #9 are now in the new Order record.
11. Additional order specific data from the Orders form (e.g. OrderAmount), are in the new record.
12. Finally, some fields that should be Customer specific (CustomerName, CustomerAddress...) are copied from the Customers table and added to the new record in the Orders table.

The process seems convoluted to me (again, db experience = low) and not normalized as I understand it.

Here is my problem: My direction is to get something that approximates the functionality of the Paradox db in a short period of time, (doesn't have to be perfect) and something that will have a familiar work flow to the end user. A lot of what I described above makes me think the db should be redesigned from scratch, but that would take more time and while quirky the current Pdox db at least provides a template for the design. However, I'm not even sure that the above process can be emulated in Access (2007).

What would be my best approach (aside from getting another job)? - Thanks

[Javier Hernández]

I haven't used Paradox, but being a relational database I don't expect the schema to be substantially different. In any case, sounds like the real work to do is in the Access forms.

Some order specific fields like SalesAssociate, and OrderDate are also entered in the Customers table via the Customers form. (they have reports showing customers with orders pending from this table)

Yes, the tables seem to need some optimization but in my experience you can port the old data to the new tables with a few queries, so every hour of work on the normalization is going to save entire days to all employees.

SalesAssociate is probably going to need its own table, with a one-to-many relationship to the Orders table. OrderDate definitely belongs to the Orders table. If you have a rule for setting the Next renewal date, a field isn't needed at all in the Customer table, it could be calculated when requested with a query. I don't see the point on entering the data twice and deleting the records and write them again. According to what you described, here it is what should happen in a optimal application with normalized tables:

1. A Orders form lets the user to enter a new one. A new record is created.
2. The client is selected on that same form, you enter all the info pertaining the order. In this form you should be able to choose the SalesAssociate too from its own table.
3. That's it. If the order information is to be printed, a report opens extracting the order data and the client data using a simple JOIN query. This query would also include data like the Next renewal date, calculated according to your company's inner policy.

[cbnsd]

Javier - thanks, appreciate the advice. Was hesitant to redesign and port the old data, but it's probably the right thing to do.

[orange]

This may be of some interest. I just found it via google. I have never used it.