The Slideshow Database Structure

You can store slide information in a database in many different ways. For this chapter, you ll create two related database tables, a Slideshows table and a Slides table. The Slideshows table will contain a list of titles for the slideshows you want to create. For example, entries in the Slideshows table might be Mexico vacation, Grandma s 70th birthday party, and Sabrina s graduation.

The Slides table will contain entries for individual slides. For each slide, you ll store the path or URL of the slide (where the picture is on your site). You don t store the actual graphics file for the slide in the table. Instead, you store just a reference to the graphics file s location on disk. For each slide, you ll also specify what slideshow the slide belongs to. Optionally, you ll also be able to specify a caption and date for each slide. Figure 12-2 gives you an overview of the two tables you ll work with.

Figure 12-2: The two-table database structure for slideshows.

The two tables are said to be related because they have data in common namely, the slideshow name. The common data gives the tables what s often referred to as a master-details relationship. Each slideshow entry in the Slideshows table is a master record. The records in the Slides table that have the same slideshow name belong together and are the detail records for a given slideshow.

Entries in the Slides table don t necessarily correspond one-to-one with graphics files on disk. If you want the same picture to appear in more than one slideshow, you can make separate entries for it in the Slides table. The URL for the slide would be the same, but the slideshow name would be different, and if you wanted, you could have a different caption for the slide in each slideshow.

Note 

Entering the same picture multiple times into the Slides table is, strictly speaking, against the rules of rigorous relational database design. However, I m going to suggest that we use a simplified database structure for your slides. See the following sidebar for more details.

About the Slideshow Database Design

The two-table design for the slideshow tables in this chapter is not optimal. When creating the database design for this chapter, I made some decisions that I feel need to be explained. A database expert would quickly point out that the information in the Slides table is not normalized, meaning that the table might contain duplicate information. For example, you can enter information about the same picture twice or more into the Slides table to display the same slide in different slideshows. A more rigorous database design than mine would call for three tables one for the slideshows, one for individual pictures, and a third to tie together the pictures and the slideshow names into slideshows. A three-table design would make it possible not to repeat a picture in the Slides table.

I ve elected to go with the unoptimal model described in this chapter for two reasons. The first reason is that I didn t want to get into the complexities of database design in this book. Frankly, database design and structures can become quite involved, and mastering databases is not the focus of this book. A second reason is that I want to keep the pages we re creating as straightforward as possible. I ve therefore stayed away from a database design that would require joining multiple tables to create a result set for the pages and, especially, that would make it difficult to enter and edit slideshow information in the database.

Another design aspect of the Slides table, as noted in this chapter, is that the table contains a reference to the location of the slide instead of containing the binary representation of the picture itself. Although it s possible to store binary data such as pictures in a database, doing so has some disadvantages. One is that storing binary data quickly bloats the size of the database. Another disadvantage is that reading and writing binary data in a database is comparatively complex. On the other hand, storing just a reference to the path of the graphics file is easy. Storing a reference has the disadvantage that if you decide to move or delete the graphics file for a slide, the database will still contain the old path. For the pages we re creating in this chapter, we ll live with that restriction. If you move a graphics file, you ll have to update the database with the new location.

In conclusion: for the types of pages and applications we are creating in this chapter, I believe that it s acceptable to trade off theoretical rigor for practicality. And let me reiterate that the table design for the slideshows is my personal design; it doesn t represent any limitations in Web Matrix or Microsoft ASP.NET.