multi-shop orders table and sequential order numbers based on shop
- by imanc
Hey,
I am looking at building a shop solution that needs to be scalable. Currently it retrieves 1-2000 orders on average per day across multiple country based shops (e.g. uk, us, de, dk, es etc.) but this order could be 10x this amount in two years.
I am looking at either using separate country-shop databases to store the orders tables, or looking to combine all into one order table.
If all orders exist in one table with a global ID (auto num) and country ID (e.g uk,de,dk etc.), each countries orders would also need to have sequential ordering. So in essence, we'd have to have a global ID and a country order ID, with the country order ID being sequential for countries only, e.g.
global ID = 1000, country = UK, country order ID = 1000
global ID = 1001, country = DE, country order ID = 1000
global ID = 1002, country = DE, country order ID = 1001
global ID = 1003, country = DE, country order ID = 1002
global ID = 1004, country = UK, country order ID = 1001
THe global ID would be DB generated and not something I would need to worry about. But I am thinking that I'd have to do a query to get the current country order based ID+1 to find the next sequential number. Two things concern me about this: 1) query times when the table has potentially millions of rows of data and I'm doing a read before a write, 2) the potential for ID number clashes due to simultaneous writes/reads.
With a MyISAM table the entire table could be locked whilst the last country order + 1 is retrieved, to prevent ID number clashes.
I am wondering if anyone knows of a more elegant solution?
Cheers,
imanc
