Database Design
The crucial first step in constructing any database is database design. You can’t create a functional, efficient database without first thinking through all its components and desired functionality in great detail. Chapter 2 of the Data Definition Guide provides a good introduction to the topic.
A Quick Look at Data Modeling
This following list provides a brief and simple overview of the process of designing a database:
- Determine data content: What information needs to be stored? In thinking about this, look at it from the end user’s point of view: What groups of end users will access the database? What information will they need to retrieve? What questions will they be asking of the database?
- Group types of data together: Information items tend to group together naturally. Later in this Quick start, when you create tables in the database, you will create one table for each group of data items. The granularity with which you divide the mass of information into groups depends on factors such as the quantity and complexity of the information your database must handle. The goal is to have each item of information in only one place. The process of identifying such groups is called normalization. Identify entities and their attributes. In this Quick Start, for example, one type of entity is the project. A project’s attributes are its ID number, name, description, leader’s name, and product. Later you will see that there is a table named Project that has columns named proj_id, proj_name, proj_desc, team_leader, and product.
- Design the tables: Determine what tables you will create, what columns will be in each table, and what type of data each column will contain. If you have identified your entities and their attributes carefully, each entity corresponds to a table and each attribute is a column in that table. At this point you then decide on the datatype for each column, as well.
- Is the data numeric or text? If it’s numeric, what is the expected range of values? If it’s text, how long a string do you need? Identify an appropriate datatype for each column. InterBase’s supported datatypes are discussed in Chapter 4 of the Data Definition Guide.
- Consider the interdependencies of your table columns: You can’t sell an item, for example, unless you have it in an inventory. You can’t deliver it unless it’s in stock. You create primary keys and foreign keys to maintain these dependencies. This is called maintaining database integrity. Other mechanisms for maintaining database integrity and security include CHECK constraints, and using GRANT and ROLE statements to control access to tables.
The TUTORIAL Database
The TUTORIAL database that you create is, in fact, an exact copy of the EMPLOYEE database that is used for examples throughout the InterBase document set. The TUTORIAL database is a generic business database. Imagine that you are responsible for creating a database for this company. In the data modeling phase, you identify the following entities (information groups):
- departments
- jobs
- countries
- customers
- salary history for each employee
- projects
- employee projects
- sales
- department budgets for each project
- employees
As you progress through this Quick Start, you will see that the TUTORIAL database contains ten tables that correspond exactly to the ten items above. To get an overview now, you can look at the EMPLOYEE database, since the EMPLOYEE database and the finished TUTORIAL database are identical. The EMPLOYEE database is located in the \Documents and Settings\All Users\Application Data\Embarcadero\InterBase\gds_db \examples directory on your machine if you installed the examples.