CODEWITHSUNDEEP
databasedatabase-designdata-modelingrelational-database
swisscheese
swisscheese

Reputation: 1775

Data Modeling Verification

Looking for advice on the best way to model the following generic requirements. Since these are just generic only basic entities/attributes are included in the model.

The purpose is to capture and list websites for businesses that may or may not have franchises.

  1. A business may have zero, one, or many websites
  2. Franchises (reason for including ExternalBusinessId) of the business may or may not share the same websites as the Business itself or other franchises

In my attempt to fulfill these requirements I removed ExternalBusinessId from the PK of Website. Not sure if it is that simple to meet these requirements, but it looks like it would still leave a lot of dups.

Another approach that I may need to take is to move the franchises to their own table which could make this problem easier to solve but complicate the rest of my model (not shown here). If having Franchises in their own table is the right approach I would rather go that route and go through the rest of the exercise of having that fit into my complete model. In my current model the way to handle businesses without franchises they are given a default ExternalBusinessId of 001.

Any thoughts?

Thanks

enter image description here

Upvotes: 1

Views: 902

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95522

  • A franchise is a business.
  • The word franchise describes a relationship between two businesses.
  • Every business has zero, one, or many websites.

If I understand you correctly, you seem to think something like this.

  • Storing franchises in a separate table implies eliminating them from the table "business".

Franchises are businesses. Store them in the table "business", just like every other business. Store the relationship between a franchise and its franchiser in another table.

Information related to the franchise as a business should reference a key in the table "business". Such information might include its mailing address and phone numbers.

Information related only to the franchise as a franchise should reference a key in the table of franchises. Such information might include the franchise license number and franchise termination date.

Upvotes: 1

Related Questions