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
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
Any ideas on how this can be made better?
Update: Perhaps a more realistic example could be
Walmart Inc. (A). They have
Walmart International, and
Sam's Club U.S. store groups (B). Totaling up to 11k stores (C).