Understanding Databases – Tables Design
Create a table
To create a table, click ‘Tables’ in the Database area. Then click ‘Create Table in Design View’ from the Tasks area to open table design window.
In this example, we’re going to create a table that holds data on company employees.
It will contain basic details on employees such as name, gender, start date, end date, address, phone number and department.
As mentioned earlier, it is a good idea to separate different types of data into different tables, so we won’t include salary data or bonus payments in this table.
We are using design view to create the table as it gives you more versatility than the ‘wizard’ option.
Specify fields with data types
Click on a cell in the Field Name column and give the data field a name.
Click in the next column ‘Field Type’ and select type of data from drop down menu. If you want the table to auto-populate with unique ascending values, select Autovalue ‘Yes’.
Finally, type a description of the data field in the ‘Description’ column.
Add desired fields to Field Name column.
Select type of data e.g. Text, Integer (whole number), Number, Date, Time, Yes/No, etc.
If it’s a field that can’t be left blank (null), click ‘Entry required’ drop down and select ‘Yes’
Name a table
Click the disc icon to save and name table. Type in the name you want to give the table and click OK to save the table.
Apply field property settings
Click on the field you want to edit. Adjust settings in the Field Properties area.
To specify the length of field entry, e.g. 2 digits, enter ‘2’ in the box.
Set a default value and change formatting if required.
Consequences of changing data types and field properties
Once the database is created and tables have been populated, changing the data types and field properties can have a range of consequences for the database.
Links are created between tables by creating relationships. If properties are changed this can invalidate the links, causing problems with queries and reports.
The more complex a database becomes, the more serious the consequences of changing data types and properties can be.
Spend extra time and thought at the beginning planning your database tables so that changes are unlikely further down the line.