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:
You first have to create a database before you create the tables.
We will be creating the following four tables in Microsoft Access:
The Human Resources table will store employee information.
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:
The Information Services table will all Information Services to store assets (i.e., computer, phone,etc.) assigned to employees.
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.
The Department Site Management table will allow each department to update data contained in its intranet site by clicking on each tab:
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 |
The Username/Password table will store employee username and passwords as the phrase implies to allow the users to log in and log out.
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.
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.
We will be populating these tables in upcoming tutorials.
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: