Best practice to handle poor data quality in relationships?

[wysiwyg6000]

I'm attempting to normalize an enormous table with order data, but I'm running into some problems. The table currently contains many duplicates, of which also included the actual order information (yikes!), but I managed to normalize it almost all the way down. It appears that different accounts can be used on orders, and these order numbers are being recycled for some reason months down the line (don't ask my why they're reusing them for future orders because I have no idea either, they should be creating new order numbers). Of course, the Order number is the primary key in my table as it should be. I guess the same thing can occur with the sales rep. Anyway, I'm struggling to find the "best practice way" to deal with this situation. I'm almost tempted to create an intermediary "transaction table" or something like that between the main general order information (which at this point will basically be the Order Number and Customer ID only), then include a table with the account information and sales rep info, then have that link to the Order Detail with the products, quantity, order number and various dates for those order numbers. Order maybe it should be a separate, related table, but not between the general order information and the order details? Can anyone tell me if I'm on the right track for this situation? It was a total curveball that the rep and account information could be different on these orders.

Option 1:
Order (Order #, Customer) -> Transaction Information (Order #, Account Type, Sales Person) -> Order Details (dates, products, quantities, etc)

Option 2:
Order Table (Order #, Customer)---> Transaction Information Table (Order #, Account Type, Sales Person)
|
|-> Order Details Table (dates, products, quantities, etc)

[Access_Issues]

If you're still looking for an answer...As a general rule I usually put any information that could repeat in its own table. Such as sales rep...if you create a sales rep table then it can store all the information for each rep without creating duplicates. Within the order, you would just reference the salesrepID (primary key).

Here are the general tables and some fields I would base it on...
1. Customer Information (if customers will have multiple contact methods I would suggest making that another table)
2. Sales Rep Information
3. Orders - order details as well as any other information for the order such as order date, ship date, total cost, shipping cost, etc (including unique value/primary key for customer, sales rep)
4. Order Details - quanties, discounts on items, etc (including unique value for products)
5. Products - description, cost, weight
6. Payment Details - you can go two ways with this...save payment information for returning customers in one table and save payment log with reference to the order in another table. If you don't save customer information you could save payment receipt information under "Orders."