|
|
Before you even start to think about data tables or any database-specific tasks, you need to clearly understand what you want to achieve. The way you design the product catalog and its data structures depends (as usual) on the client's requirements. For this case study, we'll cheat a little bit and suppose the client wants such a product catalog that allows just enough (but not too many) features to explore in this chapter.
So, what kind of product catalog does your client want? Well, the requirements for the structure of the product catalog are simple enough:
The product catalog needs to store information about products, product categories, and departments.
Products, categories, and departments have names and descriptions.
A product can be on promotion for the entire catalog or only for the departments to which it belongs.
The relationships between products, categories, and departments are as follows:
A product can belong to at least one category.
A category can contain zero or more products.
A category always belongs to a department.
For example, your store will have a Full Costumes department, which will contain a number of categories, such as Exotic Costumes and Scary Costumes. The product catalog will contain products such as Cleopatra Kit and Vampire Disguise. If you were to build an e-commerce store based on your database, the information in these tables might be used to generate a product catalog looking something like Figure 14-1.
| Tip |
Figure 14-1 shows the e-commerce store created in Beginning ASP.NET E-Commerce, Second Edition (Apress, 2003). |
You can see the list of departments listed in the left side of the page. Underneath, you can see the list of categories that belong to the currently selected department (which in this case is Jokes and Tricks). The rest of the page is filled with products.
When a department is selected, the page is filled with the products that are on promotion for that department, and its list of categories also appears. When a category is then selected, the page is populated with all the products that belong to that category.
You know a product can also be on catalog promotion. Such a product will be listed in the front page of the site.
To store this information, you'll need at least three data tables: a Department table, a Category table, and a Product table. To decide how to implement these tables and their relationships, let's first try to visualize them using some simple diagrams.
We decided earlier that a department can contain multiple categories, but a category always belongs to a single department. This is a one-to-many relationship, and it can be imagined as in Figure 14-2 (one department, many categories).
Remember that you implement one-to-many relationships by adding a FOREIGN KEY constraint to the table in the many side of the relationship, which references the primary key of the other table. In this case, you'd implement the relationship like in Figure 14-3.
Both the Department and Category tables contain two properties: Name and Description. For both tables, Name is obligatory, and Description accepts NULLs. The Category table also contains a column named DepartmentID, which references the DepartmentID column in Department.
Between products and categories, you have a different kind of relationship because a product can belong to (relate to) many categories, and also a category can contain (relate to) many products. Figure 14-4 represents how categories and products are related.
Yep, this looks like a many-to-many relationship. You'll recall that many-to-many relationships are physically implemented using a third table, called a junction table (often referred to as a mapping table). In this case, the junction table will be named ProductCategory.
Let's take a look at the complete database diagram now. Notice the two one-to-many relationships between the junction table (ProductCategory) and the Category and Product tables in Figure 14-5.
ProductCategory has a primary key made from two fields, ProductID and CategoryID (remember that you can't have more than one primary key in a table!). Each record of this table will associate a product with a category. Because the ProductID/CategoryID pair forms the primary key, you're not allowed to have identical entries in this table. In other words, each row in this table uniquely associates one product with one category.
The Product table has more fields than the other tables. The following summarizes each of the fields in the table:
Price: This field stores the price of the product.
ImagePath: This field stores the hard disk location (usually a relative path) of a file containing the product's image. This solution is often used instead of storing the image directly into the database because of performance issues. Databases are generally not optimized for storing large binary data.
OnCatalogPromotion: This field stores a binary value (zero or one), specifying if the product is in promotion for the entire catalog.
OnDepartmentPromotion: This field is similar to OnCatalogPromotion, but it specifies if the product is on promotion for the departments that contain it.
Using fields such as OnCatalogPromotion and OnDepartmentPromotion is one of the ways of tracking which products are featured on a site and where they're featured. For example, in an e-commerce Web site, on the front page of the site you'd see the products that have the OnCatalogPromotion bit set to one. When the visitor is selecting one of the departments, only the products that have the OnDepartmentPromotion bit set to one appear. Finally, when a category is selected from one of the departments, all the products in that category are listed.
|
|
