Making a conceptual database for a video rental company

I need to make a conceptual database for a video rental company. The primary worry is with the aggregate of which movies are in stock and what movies are out. I am having a hard time implementing this. Any suggestions?

    Requires Free Membership to View

    Login

One disclaimer. I design database by the "put it where it belongs" rule. That means that I don't do "pure" physical database designs. You may see 3rd normal form, you may see 2nd normal form, you may see a conglomeration of everything together. You won't see any database design that I do in an academic institution. But, the designs will work, and they will perform well.

There are several ways. You could create an inventory table that describes each title and includes a column that contains the total quantity on hand. You could create a second table that contains a list of the customers who checked out a title which should simply be something like a CustomerID and a TitleID. When they check a title out, you put it into this table. When they check it back in, you remove it from this table and attach it to the historical customer data you are probably tracking to gather buying habits for future planning. To find out what you have remaining in stock, you simply subtract the sum of the checked out items from the quantity in your inventory table. You could also go another route and stick two columns into the inventory table. One with the total qty and the second with the qty on hand. Then you simply decrement the qty on hand column to keep the totals up to date on a check out, and increment it on a check in.

 

For More Information

This was first published in May 2001

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top