In many database designs, information is distributed to multiple tables. The following image shows two tables, one for employee information and one for employee addresses:

In this example, each table contains a column named EmpID. This column associates a row of the employees table with a row in the addresses table.

For example, to obtain all information about an employee, you request a row from the employees table and the row from the addresses table with the same value for EmpID.

One advantage of using multiple tables is that you can add tables containing new information without modifying the structure of your existing tables. For example, to add payroll information, you add a new table to the database where the first column contains the employee's ID and the columns contain current salary, previous salary, bonus payment, and 401(k) percent.

Also, an access to a small table is more efficient than an access to a large table. Therefore, if you update the street address of an employee, you update only the addresses table, without having to access any other table in the database.

Database permissions

In many database environments, a database administrator defines the access privileges for users accessing the database, usually through username and password. When a person attempts to connect to a database, the database ensures that the username and password are valid and then imposes access requirements on the user.

Privileges can restrict user access so that a user can do the following:

• Read data.
• Read data and add rows.
• Read data, add rows, modify existing tables.

In ColdFusion, you use the ColdFusion Administrator to define database connections, called data sources. As part of defining these connections, you specify the username and password used by ColdFusion to connect to the database. The database can then control access based on this username and password.

For more information on creating a data source, see Configuring and Administering ColdFusion.