Before you start implementing your tables in the database, you should always spend some time design your tables properly using a design tool like, e.g., ERwin, Toad Data Modeler, PowerDesigner, Visio, etc. This is called Database Modeling.

The CREATE TABLE statement is used to create a table in a database.

Syntax:

The data type specifies what type of data the column can hold.

You have special data types for numbers, text dates, etc. Examples:

• Numbers: int, float
• Text/Stings: varchar(X) – where X is the length of the string
• Dates: datetime
• etc.

Example:

We want to create a table called “CUSTOMER” which has the following columns and data types:

Best practice:

When creating tables you should consider following these guidelines:

• Tables: Use upper case and singular form in table names – not plural, e. g., “STUDENT” (not students)

• Columns: Use Pascal notation, g., “StudentId”
• Primary Key:
  • If the table name is “COURSE”, name the Primary Key column “CourseId”,
    • “Always” use Integer and Identity(1,1) for Primary Use UNIQUE constraint for other columns that needs to be unique, e.g. RoomNumber

• Specify Required Columns (NOT NULL) – e., which columns that need to have data or not
• Standardize on few/these Data Types: int, float, varchar(x), datetime, bit
• Use English for table and column names
• Avoid abbreviations! (Use RoomNumber – not RoomNo, RoomNr, …)

Database Modelling

As mention in the beginning of the chapter, you should always start with database modelling before you start implementing the tables in a database system.

Below we see a database model in created with ERwin.

With this tool we can transfer the database model as tables into different database systems, such as e.g., SQL Server. CA ERwin Data Modeler Community Edition is free with a 25 objects limit. It has support for Oracle, SQL Server, MySQL, ODBC and Sybase.

Below we see the same tables inside the design tool in SQL Server.