Laying the Groundwork

3 4

As you begin to design your database tables, you must make several decisions regarding their structure. These decisions include determining what pieces of data need to be stored in your tables and how your tables should relate to each other. This process will help you envision the big picture before you get into the details of creating the tables. The following list provides an overview of these design decisions:

• What data will each table contain?
• What columns should be created to hold the data, and what should they be named?
• What are the requirements for the range of data that a column should be allowed to hold, and what Microsoft SQL Server 2000 data type should be used for each column?
• Are there any columns that must be allowed to contain null values, or can defaults be used instead? (Allowing null values requires more processing overhead than does using defaults.)
• Which columns will be primary keys, and which will be foreign keys?
• What kinds of constraints should be used?
• What type of index or indexes (clustered or nonclustered) should the table have, and on which column or columns should these indexes be defined?
• Which users should have access to which tables?

Try to resolve as many of these design issues as possible, and track them on paper or with online diagrams to understand the overall design of your database tables before you create them. You should also find out from your users how the data will be accessed. For example, find out if a particular table data will be read-only or if inserts, deletes, and updates will be performed. Determine which queries will be performed most often and which columns will need to be retrieved. Establish what information is really needed in the database and what is not necessary to store. This information will help you decide how to build tables and indexes, what constraints might be needed, where defaults might be useful, and more. Now let's begin learning how to create tables from the ground up.