Perhaps a silly question. I am building a database where I keep a number of stores in a Store
table. Each store obviously have some data as geo coordinates, address etc. Right now these stores are stores with actual products in Product
table.
My dilemma is that some of these stores are part of a larger group for example A
companies group has B
store group and B
store group has 100 stores. I am not sure exactly what would be the best way to organize this.
First option is perhaps to create a parent_id
column to Store
table and add a store
without any products and make it parent to many other stores, and add company groups as parent to those store
entries. I can add an extra boolean column store_group
to mark such entries as groups. However I feel this may be having too many different logical entities in the same table perhaps?
Second option is to introduce a StoreGroup
table where I keep the companies (A) and their store groups (B). Within StoreGroup
I self-reference to itself using a parent_id
column to associate (A) with (B). Then use a parent_id
column in Store
table to point stores (C) to store group (B).
This seems to be more logical as companies and store groups are logically different than stores. As they do not have products directly.
Could there be a better name? Company
instead of StoreGroup
perhaps?
Any ideas on how this can be made better?
Thanks!
Update: Perhaps a more realistic example could be Walmart Inc.
(A). They have Walmart U.S.
, Walmart International
, and Sam's Club U.S.
store groups (B). Totaling up to 11k stores (C).