CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-database
mallix
mallix

Reputation: 1429

Database design (MySQL) - standalone and chain of shops

I have an application which handles the creation of different shop entities.

The process:
1. User registers to the website
2. User creates a shop (with various attributes)

What I have so far for database tables is: 

[USER]  
user_id

[USER_TO_SHOP]  
user_id  
shop_id

[SHOP]  
shop_id

The above design covers the need for 1 user to have many shop entities under their account.
What I want to achieve now, is to have shop entities which are standalone but also have shop entities which are a part of group of shops

Chain of Shops example:
McDonalds Address X Chicago
McDonalds Address X New York
McDonalds Address X Boston

How should I proceed with my database design in order to support chain of shops but also standalone ones? Best practices are really appreciated!

*by standalone I mean a shop entity that does not belong to a chain

Upvotes: 0

Views: 342

Answers (3)

Mathew
Mathew

Reputation: 8289

From what you're saying, a shop can belong to between 0 and 1 chains? If so, I would simply add one extra table and add a foreign key to the shop table:

[CHAIN]
chain_id
chain_name

[SHOP]
shop_id
chain_id

Upvotes: 0

Remear
Remear

Reputation: 1937

Off the top of my head I'd have a locations table and rename shop to company so single companies go into that table but can have multiple locations. Then users are associated with a company location.

There are many ways to achieve what you want, so, answers will be subjective since we don't really have the whole picture.

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60528

Well, for chains, you could have something like this, just like you have for users:

[CHAIN]
chain_id

[CHAIN_TO_SHOP]
chain_id
shop_id

Not sure what you mean by a standalone shop though - if you mean a shop that isn't owned by a user or a chain, could be something like this:

[STANDALONE_SHOP]
shop_id

But I would question the need for such a table, since a standalone shop could be assumed by an absence of an entry in CHAIN_TO_SHOP or USER_TO_SHOP

Upvotes: 0

Related Questions