1.2 Planning a Database

Database creation begins with the design, which includes the following steps:

 domain research;

 data analysis (entities and their attributes);

 defining relationships between entities and the definition of primary and foreign keys.

During the design we can define a structure of relational database (composition of the tables, their structure and logical relations). The table structure is determined by the composition of the column data type and size of columns, table keys.

Break up information as much as possible. The same information stored in a poorly designed table and in a well-designed table.

Name	Address
John Smith	408 W. Park, Lincoln, NE 68522
Angie Johnson	100 E. Central, Minneapolis, MN 55413
George Ecks	503 3rd Street, Houston, TX 77338

Bad Table Design

Name	Last	Address	City	State	Zip
John	Smith	408 W. Park	Lincoln	NE	68522
Angie	Johnson	100 E. Central	Minneapolis	MN	55413
George	Ecks	503 3rd Street	Houston	TX	77338

Good Table Design

Figure 1.3 – Example of bad and good table design

Use multiple tables so that you don't duplicate information.

Customers and Invoices
Company	Phone	Invoices	Date	Cost
ACME Widgets	(800) 555-1818	1006	4/5/2011	14,00
ACME Widgets	(800) 555-1818	1201	3/1/2011	5,00
ACME Widgets	(800) 555-1818	1375	5/15/2012	12,50
Green Tea Inc.	(612) 555-7688	1131	8/1/2012	5,50
Green Tea Inc.	(612) 555-7688	1256	10/15/2012	14,00

Bad Table Design

Customers				Invoices
ID	Company	Phone		ID	Invoices	Date	Cost
5	ACME Widgets	( 800) 555-1818		5	1006	4/5/2011	14,00
6	Green Tea Inc.	(612) 555-7688		5	1201	3/1/2011	5,00
				5	1375	5/15/2012	12,50
				6	1131	8/1/2012	5,50
				6	1256	10/15/2012	14,00

Good Table Design

Figure 1.4 – Example of bad and good multiple table design

Although you can always make changes to a database, a little ahead before you create a database can save you lots of time and headaches later on.

Here are some guidelines for creating a well-designed database:

• Determine the Purpose of the Database. The best way to do this is to write down a list of the reports and lists that you want to come out of the database. This may seem a little backward at first, but if you think about it, these reports are really the reason you're creating the database. Make a list of the reports and lists you want to see and then sketch some samples of these reports and lists be as detailed as possible. This will help determine the tables and fields to include in your database.

• Determine the Fields You Need. This should be an easy step once you have determined the purpose of your database and have sketched some sample reports and lists. Think about the data type for each type of your fields. Write down the data type next to each field.

• Determine the Tables You Need. Each table in the Database should be based on only one subject. By breaking each subject into its own table you avoid redundant information and make the database more organized. The second database in figure 1.4 is broken down into two tables, Customers and Invoices, so there isn't any duplicated data. When you brainstorm, try to break down your information as much as possible.

• Determine the Primary Key. Each record in a table should have a primary key that uniquely identifies it. When you think about a primary key field, think unique each primary key value must be the only one of its kind in a table. A customer ID or invoice number would be two good examples of fields that could be used as a table's primary key.

• Determine the Relationship between tables. In figure 1.4, the ID field links the Customers and Invoices tables together. One of the linked fields should be the table's primary key.

• Sketch a Diagram of Your Database. Create a diagram of your database. Draw a box for each of your tables and write the table's field names inside that box. Draw a line between the related fields in the tables. For example, in figure 1.4, each record in the Customers table is related to one or more records in the Invoices table.