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?


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

  • Is a "store group" also a store on its own? – a_horse_with_no_name May 1 '19 at 4:58
  • It is a quite usual situation for large companies to have umbrella organizations for their stores. I put Walmart Inc. as an example to the original question. So the answer is no, store groups are not stores, and they do not sell products. Only stores which belong to the store groups sell products. – yurtesen May 2 '19 at 7:39

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)
  • Well, this is more or less what I thought about as a solution in my second option? Alternatively I thought now that I could add a boolean store_group column to mark such stores in the original Store table. That should end up with same result... – yurtesen May 3 '19 at 5:32
  • I mark it as correct anyway. Your time is not wasted :) – yurtesen May 7 '19 at 8:57

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.