How to properly handle rental inventory via database
I am building a rental inventory app that handles the tracking of rental
reservations. I think I might be over thinking it but I am getting stuck
on trying to figure out the model or schema of inventory scheduling part.
In the attached image, I have 4 copies of one movie. On the first only one
copy is scheduled to be out; on the 5th, 3 copies are out; on the 6th and
10th all copies are out. Now, I would like to design the database in a way
in which I can look up a date OR date-range to see how much inventory is
available that day(s). The challenge is that there might NOT be individual
sku's or tracking for each individual rental item. So I can't treat
Movie_1 as if it has movie_1_a,movie_1_b,movie_1_c,movie_1_d. Instead I
have to treat it like Movie_1 has 4 copies and on the 5th, 3 copies are
out but we don't know which ones.
Can anyone give any suggestions on how to write the schema. How would a
sample query look like to search for availability?
No comments:
Post a Comment