I am building a database with primarily these two kind of stores:
a) Simple small family stores
b) Big and many branches stores
Those two kinds of stores are represented in a Stores table; with the b) case I'm grouping them by same store with the Branches table, something like this:
Stores table -> 'The Apparel Store'; IDStore 1
Branches table -> 'California Branch'; IDBranch 1; IDStore 1
Branches table -> 'Miami Branch'; IDBranch 2; IDStore 1
Branches table -> 'Texas Branch'; IDBranch 3; IDSTore 1
The problem for me is, is this a good approach for the single and small businesses? i.e. an Ice Cream family store in a neighborhood; I did think about just having these kind of stores in the Stores table with just one record in Branches table, but I'm worried about the simplicity of the design.
Simplicity in the sense that, I will have thousands of single little stores, they are just perfectly representated with only the Stores table. With that being said, also there is a small possibility the little ice cream store have another branch, you never know.
Is this the most simple yet flexible design?
Best Answer
What you want to think about is what attributes you would track at each of the levels ("store" and "branch")?
You don't want to repeat information in multiple places or have ambiguity around which table a certain type of data is stored.
I'd suggest that you probably need to think a little bit differently. Instead of "store" and "branch" how about "organization" and "location"? It's a subtle, but maybe an important distinction.
The location (aka branch) table would have information (columns) about where a business is conducted and perhaps how to contact them (branch phone number, for example). You might also hand location-specific information off of this table. Not necessarily just by having columns on the "location" table, but also by having relationships that refer to the location table. Things that occur to me in this vein might include store hours, inventory and sales, for example.
At the organization level you might have more broadly based information and relationships. Things like vendors, employees, and the like.
The basic idea is to split data attributes (and relationships) in a sensible way so that the things in one table belong with other things in that table. You want to avoid thinking about trying to model the same thing in two different ways because they operate at two different scales. You asked about simplicity. The simplest data model is the one that you apply consistently.