Tracking with a Database

Question

I'm not looking for the answer, I am just looking for some guidance or a little clarity here. I need to design a database as if I worked for redbox and I'm trying to track movies actors and directors. So I am assuming I need three different tables but I just don't understand how to "track" it. Would I create a custom ID for each movie and something that tracks where the kiosks are? Like I said, I think I can do this but I just fully understand it.

Any help is appreciated

Answer 1

In broad strokes here is what you need:

(Basic relational rules and strategy apply, so every table needs to have a Primary Key, and the keys will be used to relate the tables together).

movie: One row per movie, with title, rating, year, etc.

person: Add to that a related person table with one row for any person who might be a cast or crew member in any film.

credit:, credit_type Now relate Movie <-> Person Since this is a many to many relationship you need a table between the two. Typically this would be called "credit" and you need a credit_type table that will describe the credit (actor, director, writer, producer, etc).

Of course that has nothing to do with your "tracking" question. For that you would need a slew of tables:

inventory:
Here is where you have one row for every copy of a movie that exists. It should be obvious that there will be a foreign key for a movie in this table. In the real world there would be an assigned id that would then be printed out as a barcode and attached to the disk + sleeve of the physical material.

kiosk:
For every Kiosk there is a row, along with location information, which could be an address perhaps along with a note, in case there are multiple kiosks at the same location.

kiosk_bin:
For every Kiosk, you will have a 1-M bins, each with a number identifying it.

I wouldn't do it this way, but you could for simplicity add a column in kiosk_bin that would be a foreign key to the inventory table. In this way you are able to indicate that an inventory (a single copy of one particular movie) is sitting in a kiosk_bin.

member: These are the people subscribed to the service.

member_checkout: When a member gets a movie from a kiosk/kiosk_bin, a row gets created here, with the inventory_id, and the date, and the system would update the kiosk_bin row to remove the inventory_id and show that the bin is now empty and could accept another inventory copy.

As you can see, this is non-trivial. Database design of any relatively complicated business process is going to be more than 3 tables, I'm sorry to say.

Here's an ERD that illustrates some of the basic movie to credit relations I did for another similar question. The tables were named a bit differently but you should be able to match them up.