Problem solve Get help with specific problems with your technologies, process and projects.

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?

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

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.