CODEWITHSUNDEEP
databasedatabase-designmany-to-manyentity-relationshipforeign-key-relationship
MartynJones87
MartynJones87

Reputation: 457

Database Design With Multiple Many-to-Many Relationships

I am designing a database where I need the following entities:

Manufacturers: e.g. CocaCola
Brands: e.g. Diet Coke, Coke Zero
Continents: e.g. North America, Europe
Territories: e.g. United States, Canada
Regions: e.g. Alaska, California, Quebec
Suppliers

A Supplier is located within one, and only one, Region, which belongs to a Territory, which belongs to a Continent.

A Brand belongs to a Manufacturer.

Suppliers, Regions, Territories and Continents belong to at least 1 but possibly more Brands.

I can't visualise how to organise the relationships between the tables such that Suppliers could be grouped by either Brand or Manufacturer without leading to duplicates being included within Aggregate calculations when a Supplier has multiple Brands, under a single Manufacturer. I get even more confused when I try to think about adding Regions into that equation, let alone Territory or Continent.

Any help with this would be gratefully appreciated.

Upvotes: 1

Views: 2216

Answers (3)

Dmitry Ornatsky
Dmitry Ornatsky

Reputation: 2237

There's nothing wrong with that. You can have more than one relationship between two entities. Just define as many foreign keys and linking tables as you need.

And, as Flinsch has pointed out, in your case it's even simpler: you actually have only one-to-many relationships, and therefore you don't need any linking tables.

Update. To identify different relationships, think about the business meaning of the word 'belongs'. Brand can belong to the Region as in "this brand is owned by the Californian manufacturer" and as in "this brand is used to sell goods in Alaska". These are two different one-to-many relationships, not one many-to-many.

Update 2. The valid example of many-to-many is "user is allowed to read multiple files, and every file can have multiple users with 'Read' access level".

HTH

Upvotes: 1

Kalle
Kalle

Reputation: 2293

Manufacturer -> Brand -> Continent -> Territory -> Region -> Supplier Right?

Thus the following foreign keys:

Brand contains ManufacturerId
Continent contains BrandId 
Territory contains ContinentId 
Region contains TerritoryId 
Supplier contains RegionId

If for example many continents have the same brand, a relation table is needed:

Brand (id, more fields)
BrandToContinent (BrandId, ContinentId) = many to many
Continent (id, more info)

Or maybe you need to connect a brand, or a supplier, to many regions or continents, than feel free to add more foreign key references as needed!

Upvotes: 1

Neil
Neil

Reputation: 5780

Many to many relationship doesn't exist and is usually the result of being unable to comprehend how the database structure should be.

From what you've told me, sounds to me like relationships are:

One continent -> Many Regions
One region -> Many territories
One territory -> Many suppliers
One manufacturer -> One brand
One brand -> Many suppliers
One brand -> Many regions
One brand -> Many territories
One brand -> Many continents

As you can see, all relationships are one to many or one to one. Trying to 'jump' between tables will result in a seemingly 'many to many' relationship but that can't be properly represented. I've only listed the relationships that I've gathered from what you've written, but I'm sure you can think of others. Just remember that for any given table, you can have as many one to many and one to one relationships as you like. You just can't have a relationship that's 'many to many.'

Upvotes: 0

Related Questions