Editor's Note
 IT Purchases
 Linux, But Which One?
 Virtual LANs
 Free Software - II
 Database Components
 Windows Security - I
 CISN Archive
 Questionnaire
 Send Feedback


Computing & Information Services Newsletter
Naming Database Components
     
 

Database: Databases are information warehouses where the data in relation with each other are kept and where logical and physical definitions of information are maintained. Databases model the objects that are actually existent and that have relationships with each other and they model these relationships.

Database Management System (DBMS): A generic name for the collection of the software that enables one to set up, define and operate the databases.

There are more than one database components on a database; these components play a very important role in grouping the raw data to be stored in a specific format, and then turning it into actual data.

Tables:

Tables are environments where the data is stored logically. When naming the tables, one should be careful about the aggreement of the content of the tables with their names. One should inquire and learn about how many characters can be used in table names and the column names used in these tables from the table and column characteristics provided on the database that is used.

For example, we can give two different tables of sales and production departments of a corporation. You could name all your tables related to these departments as shown below:

SALES department PRODUCTION department
sl_product pr_material
sl_customer pr_material_type

Tables are generated on the database by using create table table_name. It is also possible to group the tables logically on a database. In the example provided above, the statement "sl" refers to the sales department and the statement "pr" refers to the production department. A logical grouping or arrangement is achieved as a result of using such methods of definitions.

Columns:

Columns are attributes of an entity, that is, columns describe the properties of an entity. So, the column names must be meaningful, natural and in conformity with the content. It is a good idea to decide on a standard two to four character code for each table in your database and make sure it's unique in the database. The advantage of this convention is that in multi-table queries involving joins, you don't have to worry about ambiguous column names, and don't have to use table aliases to prefix the columns.

e.g;

sl_product( sl_material(
product_no smallint, mat_no smallint,
product_nam char(20)) mat_typ smallint))

Indexes:

Indexes can't exist on their own and they are dependent on any of the tables; therefore, they are named accordingly.

e.g;

sl_product(
product_no smallint,
product_nam char(20))

Indexes can be generated as unique or non-unique (duplicate). The index naming convention is as follows:
table_name + column_name + unique/non-unique + idx

"sl_ product" table is indexed as unique according to the "product _no" column as shown below:
sl_product_product_no_idx

If you are going to use "Composite index", that is, if you want to generate index on more than one columns, you can do it as shown below:
sl_product_product_no + product_name + idx

The index naming convention is:
table_name +column_name1 +column_name2 +unique/non-unique +idx
12345

The column names are seen in the number 2 and number 3 fields; this means that at the same time index can be generated according to the "product_name" column under the "product _no" column. If you encounter a problem about the length of the names of the indexes while creating them, you should make them shorter without harming the meaningful composition of the naming convention and by taking into account the maximum name length allowed by the database.

Primary Key:

Primary key is the column or are the columns that can uniquely identify each row in a table. The naming can be as in the following way:
column_name_pk
or
pk_column_name

More than one columns can be defined compositely as "primary key". For example;
product_no_ product_name_pk
or
pk Product_no_product_name

Foreign Key:

Foreign key is used to represent at the column basis the relationships between tables that are related. The naming is as follows:
Referencing table_Column_name_Referenced table_Column_name_fk
or
fk_Referencing table_Column_name_Referenced table_Column_name

More than one columns can be defined compositely as "foreign key". For example;
product_no_ product_name_pk
or
pk product_no_product_name

Check Constraint:

Using the column names on a table, the constraints of the data can be specified, the insert value can be assigned as DEFAULT. The naming is shown below:
column_name_chk
or
column_name_def

For example, the following states that "sl_ product" table is a constraint on "product_no" column:
sl_product_no_chk

Stored Procedures (SPL):

SPLs are operations implemented as a result of a job done. The names given to SPLs should describe the tasks and operations they perform.

SPLs can be grouped logically acording to the operations they perform. For example, a logical grouping is achieved if, an SPL that performs insert operation on the production table of the sales department is named as sp_ur_insert_bil, and if, an SPL that performs delete operation is named as sp_ur_delete_bil.

SPLs can be generated at the database with the name create procedure SPL_name and they can be executed with execute SPL_name.

Triggers:

Triggers are a special kind of SPLs. As a result of a job, a job is triggered to be performed automatically at the background.

The naming conventions of triggers have the same characteristics with SPL, however one should be careful about logical grouping. Triggers always depend on a base table and can't exist on their own; therefore, it will be better to link the base table's name with the trigger name. Triggers are associated with one or more of the Insert, Update, Delete operations; so, the name of the trigger should reflect the nature of the job done with the table name.

For example; as a trigger performing insert operation on "sl_product_ins" table, performing delete operation on "sl_ product_del" table, and performing update operation on "sl_ product _ins" table is named with create trigger trigger_name on the database.

Bahar Şengün

 
     
  - TOP -  
© 2002 METU CC
Design: CC - INFO