Relational database tables

Question

Hello, I'm currently working on an ASP.Net MVC project for a software engineering class. My goal is to create a small online game rental system. I currently have a 3 tables, Movies, Games and Registrants; and I'm using LINQ-to-SQL to define each of these tables as classes for my model. So far I've created models for Movies and Games, what I would like to do when creating the Registrant model is create a relationship between Registrants and Movies and Games. What I've tried so far is to define a foreign key between the ID (the primary key in the Registrant table) and a registrantID field in both the Movies and Games. What I realized is that if I were to remove an instance of a registrant, it will delete the associated movie and/or game from the other tables. What I'm thinking of doing is creating two separate models defining rentedGames and rentedMovies and creating a relationship between those and the Games and Movies table in order to try and model a registrant renting/returning/buying movies or games from the store.

In Summary:

What I have so far:

• 3 tables: Registrants, Movies and Games.
• LINQ-to-SQL models for my inventory of movies and games.

What I'm trying to setup:

• A model for a registrant renting/returning a movie and/or game, when a game is rented/returned, a flag is placed next to the item in the inventory to indicate its status.

Question:

• Will adding separate tables to model a rented movie/game prevent items defined in my inventory models from being deleted?? i.e. when a customer returns a rented movie, the rentedMovie instance is deleted, but not the movie is is referring to in the movie inventory.

• Is there such a thing as a related table having a status flag set on the related entry, as opposed to the entry being deleted, whenever the associated entry in the other table is modified?? i.e. when a customer returns a rented movie, the rentedMovie instance sets a flag in the movie it refers to that it's available for rent, the rentedMovie instance is then deleted.

Answer 1

You're right to create separate tables for rentedGames and rentedMovies, since this model now allows for more than one movie or game of the same type being rented at the same time, which is surely more realistic than having only one instance of a particular movie or game.

This will prevent the deletion of the parent record, when the link record (rentedMovie, say) is deleted. But this deletion of the parent movie should not be happening anyway if you've set up your relationship to not 'cascade delete', and you allowed the registrantID field in the original Movies or Games tables to be nullable.

To answer your second question (which I realise assumes only one movie/game for any particlar title): the way this is normally done, if you're using link tables, which is what you want to do, is simply to delete the rentedMovie/Game record. The absence of a link record for any Movie or Game is all your code needs to determine in order to know that that movie or game is now rentable (again).

Answer 2

I know you're doing this for a class / practice, so this may not be relevant, but consider that having the rental history for things is often very useful. Because of this, you may not want to delete the rented records, but instead just mark the item as returned.

Consider:

TABLE RentalTransaction:
RentalTransactionID integer PK NOT NULL
CustomerID integer FK NOT NULL
RentedOn datetime NOT NULL
DueDate datetime NOT NULL
<..any other fields you may need..>

TABLE RentalItems:
RentedID integer PK NOT NULL
RentalTransactionID integer FK NOT NULL
RentedItemID integer FK NOT NULL
RentedQty integer NOT NULL
RentalRetuned datetime NULL

You can see if any individual item is out or not by if it's RentalReturned field is null or not. If it is nonull, then you know the item is back, and now you can aggregate rental data to see how often it goes out, what the average length of rental is, etc, etc. You would have to build in some checks to make sure you weren't renting more copies of an item than you actually have and other such things, but I think this is overall a more flexible start to a schema. It may also be overly complicated for what you're doing, but I wanted to at least bring the idea up.

Answer 3

Do you really want to delete the rentedMovie instance? How will you report on how many movies a person has rented etc?

I'd suggest rethinking your model slightly. You need somewhere to store people data, somewhere to store item data and somewhere to store people/item data as a first step.

Ignore the difference between movies and games for now - that becomes a process of normalisation once you've defined your underlying structure.

As a simple starting point you should have:

Persons 1..1 ---- 1..* Hires 0..* ---- 1..1 Items

where the Hires table is a linking table between the two others with a combined key made up of personID, ItemID and a time-stamp of some description (to allow re-renting of the same movie).

You can then look at having a separate table for item types etc.

Answer 4

I'd go about this a bit differently. First, is there a real reason to treat a Movie and a Game as separate entities? Why not have a RentableItem that can be either a movie, a game, a game machine, a Blue-Ray player, or whatever? You'd key it by an item_id field, and it would have the expected metadata (title, type, genre, rental_class, and so on). Then you need to model the fact that a Registrant rents one or more RentableItems. This can be done with a Rental table, whose rows each connect one rented RentableItem with a particular Registrant (that is, the Rental is keyed by a rental_id and it has a foreign key to RentableItem.item_id and a foreign key to Registrant.registrant_id. The Rental would also have the due date, a "returned" flag, the price of the rental, etc.

Then you know a RentableItem is not in the store if there is a Rental record whose item_id is the same as the RentableItem's and whose "returned" flag is false. You never have to modify the RentableItem table itself, just the Rental table.

Answer 5

First thing to consider is that a movie is actually two entities, title and media. Title is "Lord of the Rings", while media is a DVD you take home. One title can have many media (copies), while one media has one title. Rental table has a row for each media-rental, this table gets a new row each time a bar code is scanned on rental, while DateReturned is populated upon return. Status field in the Media table tracks the in/out status for each disc/game. If you feel that you need to track which movies were rented together to a customer, you may find that by DateRented (datetime) or add a ReceiptNumber or ShoppingBasketID to the Rental table.