<= /o:p>

The core of any software development project at the_co= mpany usually involves the storage and subsequent retrieval of data. Generally th= is is accomplished through an RDBMS (Relational DataBase Management System).

Thus, one of the first steps in a project involves com= ing up with a database schema for the project. A schema consists of the tables, datatypes, indexes, relationships, and permissions for the data stored in t= hat project.

This SOP’s scope is to cover the steps and gener= al guidelines for creating a database schema at the_company.

The basic steps are to first identify the data and relationships, but the second step is to take that data and map it to table names, column names, and datatypes.

Once these are determined, relationships and optimizat= ions including indexes, and permissions may be granted on these tables.

Table Creation

The creation of tables involves a great deal of though= t. Most of the subjective issues resolve around naming so we tackle this first= . To some degree, the naming of fields and tables is similar to the conventions = of naming classes and attributes in Java or Perl.

The rest of the table creation involves the more synta= ctical issues such as how to deal with different data types especially amongst different databases. We will note database specific issues elsewhere.

Table Name Guidelines

Prefixing

The first rule of naming tables at the_company is that= they should be prefixed with a reasonable project abbreviation. For example, tra= de finance tables prefixed with “TF”, Banking Single Sign On tables with “BSSO”, and so on.

Case and Delimiter

All table names are uppercase. Because propercase is n= ot used to distinguish between major words, underscore “_” must be used instead. For example, TF_LETTER_OF_CREDIT.

Nouns and Adjectives

The table name should at minimum be a noun indicated t= he type of set it is storing. If the noun is not descriptive enough it may als= o be preceeded by one or more adjectives.

For example, if there is one transaction table, the ta= ble might be called TRANSACTION. But if there is more than one transaction table because the data model for approved and rejected transaction storage is dissimilar then there would be two tables: APPROVED_TRANSACTION and REJECTED_TRANSACTION.

No Plurals

A table is a set of something that may contain not onl= y many elements of that set but also 0 or 1. It is not appropriate to name a set a= s a plural. For example, do not call a table ADDRESSES, call it ADDRESS.

Be Verbose But Not Too Verbose

As long as the table name does not break any hard data= base constraints, you should try not to use abbreviations for table names. The r= ule of thumb is that anything over 10-15 characters is a bit long and anything under is definitely OK.

For example, ADDRESS_BOOK is fairly long and could be shortened to ADDRESS but ADDR or another abbreviation is confusingly small. People find whole words easier to remember unless the abbreviation is widely accepted (eg ID short for identification).

Breaking a Table Into Two

Occasionally you may run into a database constraint th= at forces you to break a table into two 1-1 (one to one relationship) tables. = If this is the case, the second table may carry a name similar to the first one but with the word “_DATA” appended to it.

This would indicate that this table contains further d= ata about the previous table. For example, in our trade finance application, the letter of credit application has too much data in it, so we broke it out in= to TF_LC which stores the core LC information that is most often looked up and= the least often looked up extraneous (but still important) data is stored in TF_LC_DATA.

Break a Table Into Three

Sometimes the breakage is not very even when breaking a table into two independent entities with 1-1 relationships. In this case, t= he other table might still be too large. Therefore, it is still time to break = them up even further. One rule of thumb is to potentially name the subsequent ta= ble with a “DESC_DATA” prefix indicating that all text and blob fie= lds belong in this table while data that is more likely to be directly queried (primitive data types) for efficiency purposes remain the original split out “DATA” table.

Lookup Tables

Lookup tables are tables that exist to provide a descr= iptive name for some id or abbreviation field. For example, the types of address b= ook entries might consist of P for Personal, B for Business, and PB for Both. In this schema, there should be a lookup table which can map these abbreviatio= ns to their descriptive name.

Usually these lookup tables are suffixed with a word (= eg DESCRIPTION) that indicates the type of lookup they are doing such as CATEGORY_DESCRIPTION (a lookup of category descriptions) or PRODUCE_CODE_DESCRIPTION (a lookup of product code descriptions).

Join Tables

Another type of common table used in database schemas = is a “join” table. In other words, a join table is a table whose pur= pose is to join two other tables together into a M-N (many to many) relationship= .

For example, you might have a table of groups whose un= ique identifier is a group id and a table of addresses whose unique id is address_id. But to join them together (associate more than one group to more than one address) we need a new table to join the two together whose sole fields are address_id and group_id (used to join the two together).

The name of these tables should consist of the main en= tity followed by the second entity followed by the “MAP” suffix. For example, if ADDRESS and GROUP are joined together via a join table, this ta= ble name would be ADDRESS_GROUP_MAP. Since the table maps the two tables against each other.

Key Tables

Sybase is one of the few databases that does not have = a nice clean mechanism for autoincrementing fields. While it does have an identity column type that suffices for representing field uniqueness, it frequently jumps in value (eg +50000 at a time) when the database is shutdown and restarted (as part of its burn rate).

This is reasonable if the identity field is just used = to determine uniqueness but it is horrible when the id field is combined with a calculation to provide some deterministic transaction id or a value to give back to the user so that they can write down the id for future lookup.

Therefore, in Sybase, for schemas that contain tables = with sensitive ids we create a key table to store the key to increment. Then, th= e_company’s Java DataSource can be configured to call a stored procedure which incremen= ts the key value and returns it in a single transaction for applying to an “add” operation on that datasource.

The key table is usually called “KEY_TABLE”= ; and is prefixed with the normal project abbreviation. For example, the key table for Trade Finance is called “TF_KEY_TABLE”. It contains two columns: tablename and lastkey. “tablename” stores the table the key is being incremented for and “lastkey” contains the last autoincremented value.

Then, a stored procedure is created in the database ca= lled “GET_NEXT_KEY” (TF_GET_NEXT_KEY in the case of Trade Finance). This stored procedure is us= ed to retrieve the next key. The following lists the sample SQL code used in Sybase to create these two tables where X is the project name:

use X

create table X_KEY_TABLE (

&nbs= p; tablename varchar(25),=