Developers/Important conventions

All tables, columns, foreign key constraints and indexes follow a naming schema that allows everyone to identify their purpose without the need to look what's behind.

Note: As Arcavias uses ANSI SQL where possible, all identifier are enclosed by apostrophes (") to enforce a common naming behavior in all supported databases. If you leave out the apostrophes, MySQL would leave the name as is while Oracle would convert the whole name to upper case.

Note: The identifier names (table, column, foreign key and index names) must be 30 characters or less in length as the Oracle database allow only those 30 chars.

Tables
Table names consist of
 * a prefix, which is "mshop" for Arcavias related tables
 * the domain where the table belongs to (e.g. one of the list of domains)
 * additional parts that identify the purpose of the table

All parts (prefix, domain and additional parts) are in lower case and separated by an underscore (_). The main product table for example is named

"mshop_product"

and the list and type tables are named

"mshop_product_list" "mshop_product_type"

Note: If you are going to implement your own extension, please choose a different prefix for your tables. The prefix should be four to eight characters long and should not be used by anybody else.

As you've seen in the example, each domain consists of a main table and tables that are depending on this table. There are two types of depending tables:
 * Tables that references another table
 * Tables that are referenced

The "mshop_product_list" table is an example of a table that references the parent table (by its "parentid" column) and each record in this table depends on a record of the parent table (the "mshop_product.id" column). Contrary to that, the "mshop_product_type" table is a typical example of a table that is referenced. It's independent from any other table and contains the product types that are available for the product records. Each record in the "mshop_product" table depends on a record in the "mshop_product_type" table (by its "typeid" resp. "mshop_product_type.id" column).

Note: Records of other domains (besides the locale domain) are usually referenced via list tables because they should provide a m:n relation and be independent from those domains.

Columns
Column names are rather straight forward: They should identify the purpose of the column. As they are prefixed by an alias in statements, they don't need to be unique across tables. Instead, all tables use a set of names for the columns that are required by all tables:


 * "id" : The unique ID of the record in the table. This value should be generated automatically by the database.


 * "siteid" : The reference to "mshop_locale_site.id" to allow multiple shops in one database.


 * "typeid" : The reference to the type table if there's any.


 * "langid" : The reference to "mshop_locale_language.id" if the record is language dependent.


 * "currencyid" : The reference to "mshop_locale_currency.id" if the record is currency dependent.


 * "status" : For status values if the records should have one.


 * "ctime" : Initial creation time stamp of the record.


 * "mtime" : Last modification time stamp of the record.


 * "editor" : Login name of the last editor of the record.

Foreign key constraints
The big advantage of foreign key constraints are the checks the database performs to enforce referential integrity. Thus, we can be sure that the columns with foreign key constraints contain only allowed values.

Constraint names consists of
 * the prefix "fk" for "foreign key"
 * a short name identifying the table ([<table abbreviation (2char)]*)
 * the name of the column that contains the references

As for the table names, all parts are in lower case and separated by an underscore (_). An example for the foreign key constraint of the "siteid" column in the "mshop_product_type" table referencing the "mshop_locale_site.id" column would be

CONSTRAINT "fk_msproty_siteid" FOREIGN KEY ("siteid") REFERENCES "mshop_locale_site" ("id") ON UPDATE CASCADE ON DELETE CASCADE

Indexes
Indexes are very important to speed up database queries when using columns that are not covered by an foreign key constraint.

Index names consists of
 * the prefix "idx" for "index"
 * a short name identifying the table ([<table abbreviation (2char)]*)
 * the names of the columns covered by the index separated by underscores (_)

As for the table names, all parts are in lower case and separated by an underscore (_).

There are three types of indexes:

CONSTRAINT "pk_msproty_id" PRIMARY KEY ("id")
 * primary key : This is in fact a constraint that enforces unique, non-NULL values rather than an index but also creates an index for the column. The naming schema is the same as for the foreign key constraints besides the prefix, that is "pk", e.g. "pk_msproty_id". A primary key is usually added to the "id" column of each table and is part of the table schema in a constraint section, e.g.

CONSTRAINT "unq_msproty_sid_dom_code" UNIQUE ("siteid", "domain", "code")
 * unique index : This is also both, an index and a constraint and enforces unique values but also allows an unlimited number of NULL values in one or more columns covered by the unique index. The prefix for unique indexes is "unq", e.g. "unq_msproty_sid_dom_code" and it's defined in a constraint section of the table, e.g.

CREATE INDEX "idx_msproty_sid_status" ON "mshop_product_type" ("siteid", "status");
 * regular index : This is an index without any constraints and only used to speed up SELECT statements. The names of those indexes begins with "idx", e.g. "idx_msproty_sid_status". Regular indexes are defined outside the table definition, e.g.

Note: Indexes, regardless of what kind are most effective if they cover several columns. The order of the columns in an index should be the same as the order of the columns in the SELECT statements as databases (especially MySQL) might not be able to reorder the column names in the SELECT statements to match the order of the columns of the index.

Note: The SELECT statements used by Arcavias always start with a "siteid" condition. This means, that all unique or regular indexes you create should have "siteid" as first column of the index.

<< Previous: Domains | Next: Important structures >>