0

I am designing single database for multiple stores. Every store may have different products and stock. should i have to include store_id in every table in database. my database looks like this enter image description here

3

This problem is called multi-tenancy. You want to offer the database service to multiple 'tenants' (in your case, stores). Microsoft published some time ago a paper on the topic, Multi-Tenant Data Architecture which highlights some approaches (separate database, shared database separate schemas, shared tables) with pros and cons of each. Your approach is shared tables, and yes, you do need a store_id in table that contain per-store data (there are usually some shared tables that do no require a store_id). Not only that, but all indexes will require store_id as a leftmost key. All your JOIN must also add AND left.store_id = right.store_id as a join condition. And all your queries must have a WHERE store_id=@store_id filter.

0

If those stores are totally independent and the only reason to have them in a single databases is to share stored procs, etc then add store id to every table. But think of some possible future development. Can different stores share some product definitions? Will there be transactions referencing different stores at once? Probably you need more elaborate design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.