Dilemma: I need to store restaurants in a database and then group certain restaurants together. For example: I have restaurants: 1,2,3,4,5. I want to say that 1 & 2 are in group Atlanta, 3,4 in group New York, and 5 in group San Francisco. I also want to create a group called East Coast that will have all the restaurants in the east coast (Atlanta, New York), West Coast which will have San Francisco, and a group called National that contains: (East Coast, West Coast).
Solution: In my first try when trying to solve this problem. I created a table containing all the restaurants then more tables for city,state, east coast, west coast, national. This seems like a good database design, but it won’t hold up if you create new type of groups. So I instead minimized the design into 3 tables. One table for information about all the restaurants, another table for group names, and another table called groups. The GroupNames table stores the name of the group, for example: Atlanta, East Coast, National. The Groups table stores the restaurants that belong to a certain group name; it also stores groups names that belong to another group name. For example: group name East Coast will contain: Atlanta and New York. National will contain East Coast and West Coast. I have provided the table structure for this below.
GroupNames (CompanyID.ID + name = Unique)
———–
ID
CompanyID.ID
name
Groups (Resturants.ID = UNIQUE)
———–
ID
GroupNames.ID
Restaurants.ID
otherGN.ID (NULL) [If Exists: For all groups, do join to get all restaurants associated, also restaurants.ID above is added to the list of restaurants retreived or it can be ignored]