HOW TO CREATE A RELATIONAL DATABASE (PART 1)

Typically, the first step in creating a dynamic database (or data source) application is to create a database.  In this tutorial, will we show you how to create a Microsoft Access database for a Content Management System (CMS) later. Usually, the steps in creating a database is:

  1. Create it
  2. Relate it
  3. Populate it

Creating a Database

You first have to create a database before you create the tables.

  1. Create a folder at the top level of a server (in our case, this is the ColdfusionX/wwwroot/, where the X is a version of CF) and name it Employee Directory.
  2. Open Microsoft Access and select File > New from the menu
  3. In the New Field panel, select the Blank Database option.
  4. Type a name (in our case, we named our database rmcs_database1234.mdb) for the database and click on the Create button and save it in the folder you created earlier.
    TIP: It is important to give your database a name the can not be easily hacked. This is the reason the 1234 was added to the database name.

Creating Tables for the Database

We will be creating the following four tables in Microsoft Access:

  1. Human Resources
  2. Information Services
  3. Department Site Management
  4. Username/Password

 

Human Resources

The Human Resources table will store employee information.

  1. With the database (rmcs_database1234.mdb) still open, click on Design View to create a new table.
  2. Give the table a name of tbHumanResources
    TIP: Avoid using spaces in filenames or field names.
  3. Add the following data to the table (See MS Access Help, if necessary):
  4. Set the EmployeeID field as a primary key by right-click next to the EmployeeID field and select primary key.
    NOTE: By setting the EmployeeID to be a primary key ensures no record will be duplicated.
  5. Save table.
Human Resources Table
Field Name Data Type Field Size Required Allow Zero Length Indexed
EmployeeID Text 11 Yes No Yes(No Duplicates)
LastName Text 50 No Yes No
FirstName Text 50 No Yes No
Address Text 50 No Yes No
City Text 25 No Yes No
State Text 2 No Yes No
ZipCode Text 10 No Yes No
HomePhone Text 13 No Yes No
Title Text 50 No Yes No
Department Text 50 No Yes No
Extension Text 10 No Yes No
HireDate Data/Time N/A No Yes No
EditPages Yes/No N/A No N/A No

NOTE: The EditPages will be used in the CMS to determine who has access to edit pages. It will be used with the Department field to determine who can edit pages but what department can be edited.

NOTE COMMON TO ALL TABLE:

  • Additional fields (columns) can be added later, if necessary.
  • Is it best practice to estimate how many characters is needed for a given field because the field sizes determines the size of the database. However, if the data field is too small, Access will truncate the data is it is too large for the field.
  • Other than the primary key field, all of the fields are NOT required in the database. It is best practice to use form validation to ensure the user enter the required data. If you allow the database to determine if a field is required, if a field is left empty in the form, an error will be throw in the browser.
  • Every table has a primary key with its field name set to the name of the table and a suffix of "ID" (i.e., EmployeeID). Also its data type set to AUTONUMBER.
  • Allow Zero Length works in conjunction with the Required option. Since we don't require most of the fields except the primary key field, date/time field, or boolean (yes/no) field, we can also allow zero length.
  • The Indexed option is used for ....

Information Services

The Information Services table will all Information Services to store assets (i.e., computer, phone,etc.) assigned to employees.

  1. With the database (rmcs_database1234.mdb) still open,
  2. On the Create tab, select Create New Table
  3. Click on Design View to create a new table.
  4. Give the table a name of tbInformationServices
  5. Add the following data to the table.
  6. Set the AssetID field as a primary key by right-click next to the AssetID field and select primary key.
  7. Save table.
Information Services Table
Field Name Data Type Field Size Required Allow Zero Length Indexed
AssetID AutoNumber Long Integer N/A N/A Yes(No Duplicates)
EmployeeID Text 11 No Yes No
AssetType Text 25 No Yes No
AssetBrand Text 25 No Yes No
AssetModel Text 50 No Yes No
AssetSerial Text 50 No Yes No
DateAssigned Date/Time N/A No N/A No
DateReturned Date/Time N/A No N/A No
ReturnReason Memo N/A No Yes No


NOTE: Notice that the EmployeeID in this table is set to "Text" instead of AutoNumber like the tbHumanResources table. This is because Microsoft Access only allows for ONE AutoNumber per table.

Department Site Management

The Department Site Management table will allow each department to update data contained in its intranet site by clicking on each tab:

  1. With the database (rmcs_database1234.mdb) still open, click on Design View to create a new table.
  2. Give the table a name of tbDeptSiteManagement
  3. Add the following data to the table.
  4. Set the PageID field as a primary key by right-click next to the PageID field and select primary key.
  5. Save table.
Department Site Management Table
Field Name Data Type Field Size Required Allow Zero Length Indexed
PageID AutoNumber Long Integer N/A N/A Yes(No Duplicates)
PageTitle Text 50 No Yes No
NavLink1Text Text 100 No Yes No
NavLink1URL Text 50 No Yes No
NavLink2Text Text 50 No Yes No
NavLink2URL Text 50 No Yes No
NavLink2Text Text 50 No Yes No
NavLink2URL Text 50 No Yes No
MainPageData Memo N/A No Yes No
LastUpdated Date/Time N/A No N/A No

Username/Password

The Username/Password table will store employee username and passwords as the phrase implies to allow the users to log in and log out.

  1. With the database (rmcs_database1234.mdb) still open, click on Design View to create a new table.
  2. Give the table a name of tbUsernamePassword.
  3. Add the following data to the table.
  4. Set the UsernamePasswordID field as a primary key by right-click next to the UsernamePasswordID field and select primary key.
  5. Save table.
Human Resources Table
Field Name Data Type Field Size Required Allow Zero Length Indexed
UserNamePasswordID AutoNumber Long Integer N/A N/A Yes(No Duplicates)
EmployeeID Text 11 No Yes No
Username Text 50 No Yes No
Password Text 50 No Yes No
LastLogin Date/Time N/A No N/A No

.
NOTE: Usernames and passwords are stored in a separate table so that is it not accidently added to each employee record and displayed on a page.

 

 

Relating Tables

After a database has been created and tables inserted into it. It is important to create "relationship" between particular tables. Because EACH asset must be assigned to a user from the tbHumanResources table, a relationship between these two tables will be created. Also, because a username and password is assigned to EACH employee from the tbHumanResources table, a relationship between these two table will be created as well.

  1. With the database (rmcs_database1234.mdb) still open, select Tools > Relationships from the menu.
  2. In the Show Table dialog box that appears, select all four tables and click the Add button.
    NOTE: Tables must be saved to see all fields listed.
  3. Click on the Close button to close the Show Table dialog box.
    NOTE: You should see the four tables in the Relationships window.
  4. Click the EmployeeID field in the tbInformationServices and drag it onto the EmployeeID field in the tbHumanResources box.
    NOTE: A one-to-many relationship is created  which means that one employee record in the tbHumanResources table can have many records in the tbInformationServices table (i.e., phone, computer, etc.).
  5. In the Edit Relationships dialog box, check ALL three options and then click the Create button.
    NOTE: By checking the Referential Integrity, when a record is delete from the tbHumanResources database, the records will also be deleted in the tbInformationServices database.
  6. Click and drag the EmployeeID field in the tbUsernamePassword box onto the EmployeeID field in the tbHumanResources box.
  7. Again, select all three check boxes and click the Create button to create a relationship between these two tables.
  8. Close the Relationships window and save the file.
    NOTE: If you are prompted with a Security Alert, click on the Option button and select the "Enable this content."

Populating Tables

We will be populating these tables in upcoming tutorials.

Normalizing Data

Duplicated data can cause errors every time records in a database is inserted, updated or deleted and can lead to upredictable results such as the inability of entering data into a database. Duplicate data happens when two or more fields are inadvertently combined into a single table. To avoid this from happening, if the table is designed using a process called normalization, it will be free from duplicates. To normalize tables, you have to split the tables into separated tables with the correct fields in them. A database is said to be normalized when each fields contain one value and each table has the correct number of fields. Normalization can be summarized as follow:

  1. Each table should have a primary key that will always to unique that is used to identification a given record
  2. Each group of associated data should have its own table.
  3. Tables should be cross-reference using a primary key in the parent table and a foreign key in its child table.
  4. Only one item should be stored in each field. (i.e., you can not have two phone number is a single field).