A shop chain database with named "Other" category [closed]

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 10 years ago.

I'm designing a database for shops. There's a table for the shop branches and a table for the chain names:

Chains
id name
1  Wendy's
2  Henry's
3  Other

Branches
id chain_id name
1  1        East Hollywood  // Wendy's East Hollywood
2  1        NYC             // Wendy's NYC
3  2        NYC             // Henry's NYC

In the end, I want to display stuff like "Wendy's NYC" and "Henry's East Hollywood". This is fairly basic stuff.

My problem is that I want to have a chain called "Other", where I can dump all the sho开发者_高级运维ps which aren't numerous enough to give their own category. If a chain only has a couple of shops, it goes in "Other" so that the category list doesn't get out of hand. But I still want to be able to display something like "Mom 'n' Pops NYC". What's the best database design for something like this? Is it easier to implement this in the code instead of the database? Thanks.

---

At present you are storing the name in Chains and the location in Branches.

You want a class of stores with no name in Chains, but still have a name, therefore you need somewhere to store that name.

There are various possibilities:

• you could generalise your 'chain' table so it was not just real chain, but every store name. (You might then want another field which said whether to treat an entry as a real chain or not.
• you could make your 'branches' table store the name as well as the location for non-chain stores.
• you could have a third table for non-chain stores.

The third is probably the least useful, but any of them could work.

---

From a modelling perspective, it would be better to make the Chain <-> Branch relationship optional on both ends, instead of inventing a fake Chain called "Other". You map can map that to the database schema by declaring the chain_id column as allowing NULL. You should probably change the table name from "Branch" to "Outlet" ... because it doesn't make much sense to have a "branch" of a non-existent "chain".