Sorry if this is obvious but I'm new to database design.

A customer must make a reservation before renting an item(s), he provides details up front such as dates of reservation, item type etc. The employee checks if item is available before allowing the customer to rent it. If available he enters item id, rental date, return date, etc into the system.

Am I correct in creating two tables for this? One for Reservations(which includes the proposed rental info.) and one for Rentals (Which includes actual rental info). And If so, wouldn't these have a one to one relationship? How could I get around this one to one relationship? Should I merge the two tables?

CodePudding user response：

I might be wrong but from what i'm understanding you can have just 1 table for rentals and have a column named status as enum (0,1) 0 being available and 1 rented. I'm assuming you are not renting the same item at the same time.

CodePudding user response：

Firstly, since a reservation may never materialize as a rental, the relationship is not exactly 1:1 but 1:(0-1).

I would think that it's correct that you model them as separate entities since:

• They may have different "life cycles".
• They most likely have different properties.
• A rental will probably be related to a bunch of other entities compared to a reservation. Those FKs will make sense for rentals but not for reservations.