Creating Database Tables
| Now that we have created a database, we're ready to create our first database table. When creating a database table, we do not specify any of the data; rather, we simply specify the table's structureits columns. When creating a new table, we specify the name of the table as well as the name and type of each of the table's columns. Creating database tables through Visual Web Developer is quite easy. The Database Explorer (shown in Figure 13.7) has a Tables folder for each database. Simply right-click on the Tables folder and choose the Add a New Table option. This will bring up the database table editor, where you can specify the column names and types for the new table. After you opt to add a new table, your screen should look similar to the one in Figure 13.8. Figure 13.8. When creating a new database table, you will need to specify the column names and data types. To illustrate the steps needed to define the structure of a new database table, let's build a simple database table that could be used to store information about a book collection. When you're crafting a database table, it's important to first spend adequate time defining the attributes you want to have expressed in the database; these typically translate to columns in a table. For our book collection example, imagine that we want to capture the following information:
Now that we have defined the information to capture, the next step is to translate this into columns in a database table. We'll use a single table, called Books, that has an appropriately typed column for each of the six bits of information required. Start by adding a new column named Title that's of type nvarchar(150) and does not allow Nulls. To accomplish this, type into the Column Name text box the name of the new column, Title. Then, in the Data Type drop-down list, type in nvarchar(150). Last, uncheck the Allow Nulls check box. After you perform these steps, your screen should look similar to Figure 13.9. Figure 13.9. The Title column has been added to the table's definition. Using the same technique, create the following columns:
After adding these five additional columns, take a moment to ensure that your screen matches Figure 13.10. Figure 13.10. The table now contains six columns. At this point, go ahead and save your new table by clicking on the Save icon in the Toolbar or by going to the File menu and choosing the Save Table1 option. You will be prompted to give your table a name. Choose the name Books. Congratulations, at this point you have created your first database table! A Discussion on Database DesignWhen you're building a data-driven application, the application's database design is of paramount importance. The database's design is the set of decisions made in structuring the database; it's the process of deciding what tables you need, what columns make up these tables, and what relationships, if any, exist among the tables. If you start building your application using a poorly designed database, you'll likely run into unseen shortfalls or limitations further in the development process. The later you find a shortcoming, the more energy, time, and effort it will take to correct. Therefore, it behooves you to invest the time to properly model the data. Unfortunately, we do not have the time or space to embark on a lengthy discussion of database design techniques, methodologies, and theories; entire books have been written on this subject. There are, however, a few quick concepts that I'd like to take a moment to highlight. Uniquely Identifying Each RecordBecause we'll often be interested in accessing, updating, or deleting a particular record in a particular database table, each record should be uniquely identifiable. This can be accomplished in a number of ways, but most often is done through an Auto-increment primary key column. When you make a column a primary key column, the database will automatically enforce that each value is unique. A primary key column alone, though, still requires that you provide the unique values. When you also make the column Auto-increment, the database system will automatically provide a value for that column for each added record. In our Books database table, we do not currently have a column that is guaranteed to be unique. The Title column might be a unique identifier if you are certain no two books in your collection will ever have the same title. If you wanted to indicate that a book's title could be used to uniquely identify each record, you'd need to make the Title column the table's primary key. To accomplish this, select the Title column in the table editor in Visual Web Developer and then click the primary key icon in the toolbar (see Figure 13.11). Figure 13.11. Select a column and click the primary key icon to mark it as the table's primary key. What if a book's title, however, is not guaranteed to be unique? What if we might have two books in our collection with the same title? In that case we need to create a new column in our table, one whose explicit purpose is to uniquely identify each record. Typically, these types of columns are named TableName ID, where TableName is the name of the table the column is being added to. Furthermore, these columns are not only marked as primary key columns, but also marked as Auto-increment columns. For practice, let's add an Auto-increment, primary key column to the Books table. Call this new column BookID, set its data type to int, configure it to not allow Nulls, and mark the column as a primary key by selecting the column and clicking on the primary key icon in the toolbar.
Next, we need to mark the column as an Auto-increment column. To accomplish this, select the BookID column; this will load up the column's properties in the Column Properties pane at the bottom of the screen. Scroll down through the various properties until you reach the Identify Specification property, which will have a value of No. Change this value to Yes (see Figure 13.12). Figure 13.12. The BookID column is an Auto-increment, primary key column. At this point BookID is now an Auto-increment, primary key column. Click the Save icon in the toolbar to save the table's changes. Modeling a System's Logical Entities as Related TablesWhen deciding what tables your database should contain, typically you'll want each table to represent a logical entity in your proposed system. For example, if you were creating an application to record the books in your collection, the logical entities in your system could include
For a system that tracked information about the book's authors, genres, and publishers, we'd most likely want four tables in our database, one to represent each logical entity. In addition to having these four tables, we'd also want to indicate that they were related in a certain manner. Database systems include various techniques to indicate that a given table is related to another. For example, we would want to indicate that each record in the Books table could have one to many related records in the Authors table. This relationship would indicate who wrote the book. Similarly, we might indicate that each record in the Books table must be related to precisely one record in the Publishers table, indicating the book's publisher. While database models should ideally have a table for each logical entity and explicit relationships among the tables, most of the examples in this book will use a single-table design. This approach is not a recommended design approach; however, it makes creating, accessing, inserting, updating, and deleting the data much simpler.
|
EAN: 2147483647
Pages: 233