SQL Tables Tutorial

Creating tables in SQL is a key task in constructing a useful database. Taking the time to prepare your database table design before you actually start coding it can be extremely valuable. A well-designed database structure will be more reliable, efficient and easy to administer. Creating the table structure in SQL is straightforward if you follow a few basic rules.

Instructions

1

Design your database structure. SQL is used with Relational Database Management Systems. These types of database are defined in terms of the relationships between the different elements in the data. To create a relational database design, you need to model the data from an abstract level. There are many tools and techniques that can help you to do this, including Entity Relationship diagrams. Create your database design by deciding what the entities in your data are. These entities will correspond to the tables that you ultimately create. For example, in a customer order system, two of the entities could be "Customer" and "Order," each of which would have a dedicated table within the database.

2

Work out a set of attributes for each of your entities. These attributes will be the columns in the database tables. Each entity may have a natural set of attributes, but should also have one that acts as a unique identifier. For example, in an "Order" table, the attributes could include the order reference number and the date the order was placed. For a "Customer," the attributes could include names and addresses. For each attribute in the tables, choose an appropriate data type for numerical items, text strings or dates.

3

Figure out the Primary Keys in your database. Keys are an important element in enforcing the relationships within a database. Primary Keys make each entry in a table unique, and are commonly implemented by simply assigning the next in a series of numbers each time a new record is entered into the table. In a typical system, each "Customer" would have his own unique reference number in the database, as would each "Order," allowing these to be easily identified.

4

Work out the Foreign Keys in your data. Foreign Keys are the main way that you represent the relationships between tables, or entities, in a database. For example, one "Customer" may be associated with one or more "Orders," and most "Orders" will only be linked to a single "Customer." Including a field (i.e., attribute) in the "Order" table that lists the unique reference number (Primary Key) for the "Customer" who placed the order means that the tables are linked. It's also more efficient to include the Foreign Key in the "Order" table rather than the "Customer" one, as the relationship between "Customer" and "Order" is one-to-many.

5

Create the tables in SQL, defining the structure, columns and data types. An example SQL excerpt for a customer table would be:

CREATE TABLE 'customer'

(

'ID' smallint(6) NOT NULL auto_increment,

'name' varchar(50) NOT NULL,

PRIMARY KEY ('ID')

);

The ID column is an automatically increasing number assigned each time a new entry is added to the table. The "NOT NULL" statements simply require that any entry must have a value specified for those columns. The data types "varchar" and "smallint" are textual and numerical types specified along with the length of possible values - these vary according to your particular database system. Run the script on your database or web server and populate it with some data.