InterBase Quick Start: Part II - Database Design

From InterBase

Go Up to InterBase Quick Start: Part II - Data Definition

The first step in constructing any database is database design. You cannot create a functional, efficient database without first thinking through all its components and desired functionality in great detail. For a more detailed guide about database design, see Designing Databases in the Data Definition Guide.

A Quick Look at Data Modeling

The following list provides a brief and simple overview of the process of designing a database:

  1. Determine data content: What information needs to be stored? When thinking about this, look at it from the point of view of the end user: Which groups of end users will access the database? What information will they need to retrieve? What questions will they be asking of the database?
  2. Group types of data together: Information items tend to group together naturally. 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 has to handle. The goal is to have each item of information in one place only. The process of identifying such groups is called normalization. Identify entities and their attributes. For example, one type of entity is the project. The attributes of the project are ID number, name, description, the name of the leader, and product. Table Project with columns proj_id, proj_name, proj_desc, team_leader, and product fits that entity.
  3. Design the tables
    • Determine which tables you will create, what columns will be in each table, and what type of data each column will contain. If you identify your entities and their attributes carefully, each entity corresponds to a table and each attribute of that entity is a column in that table.
    • Decide on the data type for each column. Is the data numeric or text? If it is numeric, what is the expected range of values? If it is text, how long a string do you need? Identify an appropriate data type for each column. For more information about supported data types in InterBase, see Specifying Data Types in the Data Definition Guide.
  4. Consider the interdependencies of your table columns: For example, you cannot sell an item unless you have it in inventory. You cannot deliver it unless it is 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 during this Quick Start is an exact copy of the EMPLOYEE database that installs with InterBase.

The TUTORIAL database is a generic business database. Imagine that you are responsible for creating a database for a 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 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. The path to the EMPLOYEE database on Windows is C:\ProgramData\Embarcadero\InterBase\gds_db\examples\database\employee.gdb.

Advance To

Retrieved from "http://docwiki.embarcadero.com/InterBase/2020/e/index.php?title=InterBase_Quick_Start:_Part_II_-_Database_Design&oldid=22449"