CODEWITHSUNDEEP
database-designentity-relationshipjunction-table
cinnamond
cinnamond

Reputation: 79

Should I include a junction table with a primary key in my ski rental database schema?

I need to make the ER diagram and the relational schema of the Ski rentals. I have an Customer, Rental and Ski as a entities as well as their attributes. Customers and Rentals should have the one-to-many relationship and the relationship between Rentals and Ski should be many-to-many (one rental can have many skis and one ski can be rented to many customers). As I did some research on how to make the relational schema, I found out that it is mandatory to make the junction table Rentals_skis that include foreign keys from the Rentals and Ski. Is it necessary to add primary key to the junction table? Also, should I add new entity Rentals_skis in my ER diagram?

This is the links to my diagrams that I made using Dia.

Relational schema: https://i.sstatic.net/0LJH0.jpg

ER diagram: https://i.sstatic.net/Hv4b7.jpg

Upvotes: -1

Views: 57

Answers (1)

Kurt
Kurt

Reputation: 1748

Yes, for a m:n relationship you need to create the junction table that holds the primary key from each of the entities, e.g. {rental_id, ski_id}, and yes, it should be in your ER diagram. It might not be in a conceptual model, but it should definitely be in your logical model.

Whether or not you make those two foreign keys together into a composite primary key, or you add a surrogate key (e.g. rental_ski_id) is a design choice and up to you, but the table should have a primary key.

IMO your table names should all be singular nouns (rental, ski, rental_ski) rather than plural nouns (rentals, skis, rentals_skis).

Upvotes: 0

Related Questions