CODEWITHSUNDEEP
mysqlsqlmany-to-many
StyleSh1t
StyleSh1t

Reputation: 747

DB Design | Many to Many - companies & customers locations

Let's assume I have those tables:

Each company and each customer can have multiple locations.

What is the best & fastest way to create the above relationships, assuming that in my application I will need to search for all companies that are related to at least one location of customer's locations with id=N and vice versa?

Couldn't find a more generic title, feel free to change it to some generic one.

Upvotes: 0

Views: 83

Answers (2)

HuntsMan
HuntsMan

Reputation: 792

You can build some junction table for the company and the customers.

Companies  
id (pk)
name
phone
email

Companies_location
id (pk)
company_id (reference to Companies table id) (fk)
location_id (reference to  Location Table id) (fk)

Customers
id (pk)
name
phone
email

Customers_location
id (pk)
customer_id (reference to Customers table id) (fk)
location_id (reference to  Location Table id) (fk)

Location
id (pk)
state
city

SQL Statement :

You can use Union because they have same field.

Upvotes: 1

Mittal Patel
Mittal Patel

Reputation: 2762

As my opinion you can create table as below:

  • locations (id, city, state)
  • companies (id, name, phone, email)
  • companylocations (id, CompanyId, locationId) - Foreign key with companies & locations table
  • customers (id, name, phone, email)
  • customerlocations (id, customerId, locationId) - Foreign key with customers & locations table

Add indexes to below columns to fetch data faster

  • customerlocations (customerId, locationId)
  • companylocations (CompanyId, locationId)

id column for all the tables would be primary key so we have an index on that.

Upvotes: 3

Related Questions