How to design a database with one-to-many and then many-to-many relationship?

Question

I'm not very specialized with database design so much and I'm designing a database for the delivery company.

The situation is that we have two main actors, customer and truck driver. The customer will ask for a delivery and the truck driver must answer with an offer or a rate for that customer. The difficult part is, how to design a relationship between them, so the customer can request for a delivery and truck driver can give a good rate for that request. The customer should receive many offers from many drivers and drivers can receive many requests from other customers as well.

I thought about having Customer, Truck Driver, and Request as entities and Request_Driver for the many-to-many relationship.

Answer 1

This is a many to many relationship. You will have a Customer table and a TruckDriver table.

You will also have a table called Delivery or similar which will manage the relationship between the two.

The delivery table would have the columns

CustomerID (foreign key, referencing the PK in the customer table)
DriverID (foreign key, referencing the PK in the driver table)
Rate

If one customer can recieve an offer from the same driver more than once, it may also be worth adding a column DeliveryID as the Primary Key on the table, otherwise you can use a composite key of CustomerID, DriverID

There may be other information you want to include in your delivery table such as date of offer and a flag as to whether the offer was accepted or not but that is not specified in the question.