0

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.stack.imgur.com/zN66Q.jpg

ER diagram: https://i.stack.imgur.com/7lygV.jpg

2 Answers 2

-1

Is it necessary to add primary key to the junction table?

Every table should have a primary key defined, to follow good database design. A table without one is a sign of denormalization. In your case, the primary key of the Rental_skis junction table is natural and would be defined on (rental_id, ski_id), the combination of each foreign key field in this case. As it is not possible for the same ski_id to exist for the same rental_id more than once.

Note, by implementing this junction table, you'd no longer need the field ski_id to be stored directly in the Rentals table, and that column should be removed from that table in your design.

Also, should I add new entity Rentals_skis in my ER diagram?

I would think so.

-1

No, the `Rental_skis' is not mandatory, it is actually opposite. It will be source of problems. The ER diagram is more correct in this case and do not need to be updated.

The Rentals is already a junction table. It already has two foreign keys: customer_id and ski_id. They completely answer the question "who rented which ski at this moment of time?"

If you go by the schema showed (with Rentals_skis), then you can have a situation that ski_id in Rentals points to one ski, while ski_id in Rentals_skis points to another ski. In this case which one is correct? Which ski this customer rented?

And yes, the Rentals needs to have a primary key. You can go with rental_id, yes. But it would be better to use a compound key: customer_id, start_date or ski_id, start_date. The difference here is just point of view: what is more important - customer or ski.

8
  • The reason OP needs a junction table such as Rental_skis is for the requirements they described: "one rental can have many skis". You can't properly model that in a normalized way, in only the Rentals table alone.
    – J.D.
    May 4, 2023 at 1:21
  • "If you go by the schema showed (with Rentals_skis), then you can have a situation that ski_id in Rentals points to one ski, while ski_id in Rentals_skis points to another ski." - The ski_id would no longer be stored directly in the Rentals table, with the Rental_skis junction table implementation. I've updated my answer to explicitly mention that now.
    – J.D.
    May 4, 2023 at 1:25
  • @J.D. Yes, one customer can rent many skis. The Rentals table already takes care of it. You can have as many records with same customer_id and ski_id in Rentals as you want, they would be identified by date the customer did the renting, hence suggestion to have customer_id, start_date as natural PK for Rentals.
    – White Owl
    May 4, 2023 at 1:53
  • If you put that scenario all in the same Rentals table, you'd have to repeat the same primary key value (either (rental_id) or the natural key you just suggested (customer_id, start_date)) multiple times - once for each ski rented in the same rental, breaking the primary key. It would also be in a denormalized form.
    – J.D.
    May 4, 2023 at 2:31
  • Why? How many skis a person can rent simultaneously??? A customer John can rent a blue ski on 1st day and red ski on second day. So the PKs would be (1, '1/1/2023') and (1, '1/2/2023') - same customer_id, different dates. John cannot rent red and blue together on the same day.
    – White Owl
    May 4, 2023 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.