Schema for stores and groups of stores and companies

Question

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).

Answer 1

I would create a table store_group that represents the hierarchy of the groups using and adjacency model:

store_group (
   id integer primary key
   name text not null, -- maybe even unique?
   ... other attributes
   parent_group_id integer references store_group --- null for the "root"
)

If a group can be associated with only a single store group, make that an attribute of the store table:

store (
  id integer primary key, 
  group_id integer references store_group, 
  ... other attributes ...
) 

If group_id is null, the store does not belong to a group.

If a store can belong to multiple groups, use a m:n relation:

store (
  id integer primary key, 
  ... other attributes ...
);

store_assignment (
  store_id integer not null references store,
  group_id integer not null references store_group,
  primary key (store_id, group_id)
);

And you obviously need a m:n relation between product and store:

store_product (
  product_id integer not null references product,
  store_id integer not null references store,
  primary key (product_id, store_id)
);