Tables and Relationships for an ASP.NET AJAX Server-Centric Based Online Shopping Website

This is the second part of an eleven-part series focused on building an online shopping web site. In the previous part, we introduced the modules we will use as well as the main flow charts. In this part, we will take a closer look at the database design and describe the table structures and their relationships.

Contributed by Xianzhong Zhu
Rating:

/ 8
December 05, 2007

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Tables continued

Table 4—Structure for table ‘Product’

Field name	Type	Notes
ProductID	int	Primary Key identifying the record
Name	varchar(200)	The product name
CategoryID	int	Foreign Key, referring to the Category ID that the product belongs to
Desn	text	Detailed description for the product
Sell	varchar(200)	The name of the supplier
CreateDate	datetime	Manufacturing datetime
SellInDate	datetime	Stock datetime
Unit	varchar(50)
Quantity	int	Current quantity of this kind of product
Upper	int	The maximum stock quantity of this kind of product
Lower	int	The minimum stock quantity of this kind of product
InPrice	money	Stock price
OutPrice	money	Sale price
PictureID	int	Identifying the related Picture ID of the product. Foreign Key.
Remark	text	Memo info for the product

Table 5—Structure for table ‘Pictures’

Field name	Type	Notes
PictureID	int	Primary Key identifying the record
Desn	varchar(200)	The name of the image/picture
Type	varchar(200)	The type of the image/picture
Data	image	The factual data about the image/picture

Table 6—Structure for table ‘Comment’

Field name	Type	Notes
CommentID	int	Primary Key identifying the record
Desn	varchar(200)	Title of the comment
Body	varchar(200)	The detailed content of the comment
Date	image	The time the comment was created
ProductID	int	Identifying the Product ID that the comment belongs to. Foreign Key.
UserID	int	Identifying the User ID that makes the comment. Foreign Key.

Table 7—Structure for table ‘OrderForm’

Field name	Type	Notes
OrderFormID	int	Primary Key identifying the record
UserID	int	Identifying the UserID that places the order. Foreign Key.
TotalNumber	int	The total number of the product to buy
TotalMoney	money	The total fee of the product to buy
OrderDate	datetime	The datetime when the order is placed
State	int	Identifying the status of the order

More Tables continued

Table 8—Structure for table ‘OrderItem’

Field name	Type	Notes
OrderItemID	int	Primary Key identifying the record
ProductID	int	Identifying the UserID that places the order. Foreign Key.
OrderFormID	int	The total number of the product to buy
Number	int	The total fee of the product to buy

Table 9—Structure for table ‘News’

Field name	Type	Notes
NewsID	int	Primary Key identifying the record
Desn	Varchar(200)	Identifying the title of the news
Body	text	The detailed content of the news
Date	datetime	The date and time the news was created
UserID	int	Foreign Key that refers to the field UserID in table ‘User’

Table 10—Structure for table ‘Leaveword’

Field name	Type	Notes
LeavewordID	int	Primary Key identifying the record
Desn	Varchar(200)	Identifying the title of the left word
Body	text	The detailed content of the left word
Date	datetime	The date and time the left word was created
UserID	int	Foreign Key that refers to the field UserID in table ‘User’

Table 11—Structure for table ‘Notify’

Field name	Type	Notes
NotifyID	int	Primary Key identifying the record
Desn	Varchar(200)	Identifying the title of notification
Body	text	The detailed content of the notification
Date	datetime	The date and time the notification was created

Besides these tables, we’ve also defined more than fiftystored proceduresto modulate the design of the sample application, which, for brevity, will not be explained any more, but be leveraged in later sections.

Relationships Between Tables

Set up the Relationships Between the Tables

Next, let’s continue to take a look at the dependency relationships between the above defined tables. Readers that are familiar with the famous "Northwind" database should quickly realize that the relations between tables here are pretty similar to those and easy to follow. The following Figure 5 gives the dependency relationships between the tables.

Figure 5—the dependency relationships between the tables in database ‘ShoppingDB’

Author's Note: during the course of studying the sample application, you can use Visual Studio 2005 (I cannot open the above chart in it) or Microsoft SQL Server Management Studio to open the relation chart. The latter requires that you first close the connection with the database in VS2005, then start up Microsoft SQL Server Management Studio and attach the ShoppingDB.mdb database to it. Next, you can right-click the database, open the "property" window and switch to the "options" tab to change the "compatibility level" to "SQL Server 2005 (90)." Finally, you can get to the above relation chart by double clicking the "database relation chart" node. By the way, to reopen the database in VS2005 you have to detach it from Microsoft SQL Server Management Studio first, or else you will meet with some error.

Here there is only one point for us to remember. It is that the relations set up in the above Figure 5 must be followed in later programming to keep the related data in tables integrated and valid.