0

I have been working on a database that holds order numbers and order information. Previously I used a report that provided an order ID and a unique item ID for each item ordered. The item ID was the primary key as it was a uniquely assigned value.

This meant that one order # could have multiple items within it, each item identified by a unique item ID.

Now our requirements have changed to become more real time, which means the report that provided the unique item ID's has not run. As such I will need to generate my own unique item id's, but only within the same order #.

Additional information for those who use Amazon's MWS:

Previously I was using the Reports API to generate _GET_AMAZON_FULFILLED_SHIPMENTS_DATA_ to provide our fulfillment data while a 3rd party integrations company pulled the _GET_FLAT_FILE_ORDER_REPORT_DATA_ report to handle our merchant fulfilled orders. We are now looking to make our merchant fulfilled orders real-time using the Orders API, which unfortunately does not provide the orderItemId that is provided by the _GET_FLAT_FILE_ORDER_REPORT_DATA_ file. The problem I need to solve is related to the absence of that field.

Some sample data to work with would be:

Order-ID        Item-ID        Item
222-2212            1         redShirt
222-2216            1         redShirt
222-2212            2         blueShirt
222-2212            3         greenShirt
222-2218            1         noShirt

I would like to be able to something like: 

SELECT * from order_data WHERE Order-ID like '222-2212';

and have all 3 items returned. The problem I have is I have no idea how to provide a unique auto-incremented number based upon the count of the Order-ID. I also want to avoid using a standard auto-incremented value across all data as I could then end up with duplicate orders and would need to try and filter using DISTINCT.

Does anyone have any ideas, pointers or suggestions on how I may accomplish this? I do have the luxury of redesigning the entire database from scratch so I am open to suggestions.

--EDIT--

I am currently following up with Amazon to determine if the orderItemId returned by the orders API is randomly generated. I will post more details here when I find out.

Robert H
  • 11,520
  • 18
  • 68
  • 110
  • Do you wish to convert the `Order-ID` column to use auto-incrementing values? Duplication wouldn't be a problem if you normalized your design to use three tables: `orders`, `items`, and `order_items` with the third table being a cross-reference table facilitating an N:M relationship. – Zane Bien Jun 21 '12 at 19:56
  • No - I want to avoid auto-incrementing values if possible. It seems like the orderItemId I mentioned above may be unique (it was unique in my testing, but the documentation provided by Amazon does not state this), and if it is I will be able to use the orderItemId as a primary key as uniqueness will be guaranteed. I haven't considered splitting the database into multiple tables as I don't think there will be much gain in doing so, but I could be wrong in that :) – Robert H Jun 21 '12 at 20:27

1 Answers1

0

Turns out that the orderItemId is in fact a unique value. I was able to locate the documentation on Amazon's site under Confirming Multiple Shipments using Feeds. The statement states:

order-item-id Amazon.com's unique identifying number for an item in an order. This number is provided in your Orders Report. Note: This is not the same as the product ASIN. The order-item-id will differ from order to order.

I will be using this as my primary key, so I will not need to generate a random value as I expected this morning.

Robert H
  • 11,520
  • 18
  • 68
  • 110