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?
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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL Server guru is waiting to answer your toughest questions.
This was first published in May 2001