Should every table in my database include store id? 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
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.