Store Grouping Design Question:There is more than one way to solve a problem. Any advice appreicated.
Existing Form frmStoreBusinessInfo. Primary Key is the master Store table with primary key StoreID.
New requirement: Store Grouping
Each Store by default does not belong to a group (it is just an individual store).
But, an individual Store can become grouped with any other store(s). An “Add Store to Group” will be created, and a view only list box of the other stores in it’s group. So, if this view only list box is empty, the store is an individual store in no group. And, another store(s) can be added to this group. They both show up in the view only list box.
Once a Store becomes part of a group, all the stores in the same group will display the same view only view box (i.e. the other stores in the same group).
A Store can either be an individual (default) or belong to one (only one) group.

Design Requirements: When an individual store adds another individual store, a group is created in a tblStoreGroup table. A linked table tblGroupName is required. When adding a store to the group, check to determine that the new store to be added does not already belong to another group.

It sounds like you want a Junction Table here. While it is clear that a Group can have more than one Store, it is not as clear as to whether a Store can be in more than one Group. If this is the case, then a Junction Table is the way to go. Three Tables from your DataBase would be involved.
A Table of Store related information
A Table of Group related information
A Junction That has at minimum the following:
Table Primary Key
Foreign Key for a Store from the Stores Table
Foreign Key for a Group from the Groups Table

Other items can be added to the Junction Table as required, but often, these three items could be all that you need.

yes

you need a storegroup table

if a store can only be in one group, then you can add a storegroup field to the store table. if it can be in more than one, you need a junction table as pointed out.

personally, i would make the storegroupid NUMERIC, and add an "unassigned" group as group 0. That way all your stores can have a default of zero, so they are all members of the unassigned group by default. Things like that can make enforcing referential integrity easier than leaving nulls where the store is unassigned

Thanks for the validateion. Looks like I am on on the right track. Will post a diagram next week after finishing another upgrade.


And you brought up my next question: The "unassigned" group as zero (0) instead of Null.
If a Store belongs to a group - then all the stores in that group show up in the list View Box. A IDGroupName autocounter ID, with the autogenerated txtGroupName (the admin user can change the txtGroupName)
When creating the first pair in a group- or adding to an existing group, the list of "available stores" will be only the unassigned stores (i.e. zero or null). The Busienss Rules forbid any store from beloning to more than one group in this case.

So, the question of the initial storegroupID initial value. The numeric value sound good. The zero Vs Null question.
My personal opinion is to go with zero to match with the txtGroupName "unassigned".
While Null does define the true relationship, Zero would apper to offer better reporting and an administrator-only option to reset a group number back to zero.

Thanks for the valuabe oversight and comments. Any other considerations are greatly appreciated.