Design for a data store that can be updated and queried against at the same time

Question

We are designing a real-time inventory system for an e-commerce site that aggregates its inventory from multiple suppliers.

• Our visitors can search and purchase items from our site
• We get inventory updates from multiple suppliers on different schedules throughout the hour. The updates comes in the form of an inventory snapshot of their system. In other words, we do not get a list of what was sold or what was added from them, only a snapshot of their inventory at the time of the update.

What's the best design for this system so that we can update our inventory without disturbing the e-commerce experience?

Requirements:

1. Ensure our inventory store does not fluctuate. When we refresh our inventory with updates from our suppliers, we can't just delete the old records, then insert the new records. This will momentarily cause an inaccurate state in our inventory store.
2. Ensure there are no duplicates in the inventory store at all times.

The solution doesn't have to be restricted to database design. We are open to solutions involving the database, in-memory distributed cache or any other means.

UPDATE (@Andrew Keith): My question is focusing on how to maintain an inventory store (whether a database, or an in-memory cache) so that the inventory updates can be applied to the store while keeping the store open for e-commerce operations (e.g., search, checkout, etc).

Answer 1

Your question is too open ended. An E-Commerce system is a generic IT term used to reference to many systems. What you need to do is break down your question into more manageable sub questions so that you get meaningful answers.

The requirements that you mentioned are all simple requirements which can be easily supported by any design.

Are you interested in developing the system from scratch , or purchasing and integrating an existing e-commerce system ?

Answer 2

You've emphasized your desire for Atomicity for changes to inventory – use any of the popular DB systems and design your updates as transactions for whatever system you choose.

As for your requirements:

1. This is what atomicity guarantees. Though any particular customer query may need to wait for an inventory update to complete or it may become obsolete in an (almost) arbitrarily small amount of time. You should be re-checking inventory when a customer proceeds to the next step in your order process (anyways).
2. This isn't so hard if you use 'update' statements in the query language for whichever DB system you choose. I'm unfamiliar with a add-new-record, remove-old-record pattern for maintaining state (let alone inventory quantities).
3. This is impossible in general. You may sell x units of an item and the next inventory update you receive from your supplier(s) may decrease the available quantity of that item to some value less than x. Your only (general) response is to apologize to some number of customers, though in practice this is probably an insignificant concern.