Developers/Important structures

Common table structure
There are a few columns that are required in all tables to enable the full functionality Arcavias offers:


 * "id" : Each table should have an ID column to uniquely identify the record and most often it's of type "INTEGER". This is important because normally, the content of each table is read, altered and deleted by a separate manager class and these managers need an unique ID to operate only on the specific record. There may be exceptions of this rule if the table content only consists of key value pairs that directly depends on a parent table or if you use tables of other applications that are not normalized in that way.


 * "siteid" : Enables multiple shop instances ("sites") in one database and references the "mshop_locale_site" table directly. Its type must be "INTEGER".


 * "code" : An unique code to allow the record to be identified by external applications. The code is only unique per site and is optional if no such code exists or it doesn't make sense to add one.


 * "ctime" : Time stamp when the record was created in the database. This is an ISO time stamp (YYYY-MM-DD HH:ii:ss) and should use the UTC time zone.


 * "mtime" : Time stamp when the record was last modified in the database. This is an ISO time stamp (YYYY-MM-DD HH:ii:ss) and should use the UTC time zone.


 * "editor" : Login name of the user who created or modified the record at last.

A template of a basic table schema is:

CREATE TABLE " _ _ " ( 	-- Unique ID 	"id" INTEGER NOT NULL AUTO_INCREMENT, -- PostgreSQL: SERIAL 	-- Site ID, references mshop_locale_site 	"siteid" INTEGER NOT NULL, 	-- Unique code per site (leave out if not required) 	"code" VARCHAR(32) NOT NULL COLLATE utf8_bin, -- PostgreSQL: without collation 	-- 	-- ... more columns 	-- 	-- Creation time stamp 	"ctime" DATETIME NOT NULL, -- PostgreSQL: TIMESTAMP 	-- Modification time stamp 	"mtime" DATETIME NOT NULL, -- PostgreSQL: TIMESTAMP 	-- Last editor 	"editor" VARCHAR(255) NOT NULL, CONSTRAINT "pk_ _id" 	PRIMARY KEY ("id"), CONSTRAINT "unq__siteid_code" -- leave out if not required 	UNIQUE ("siteid", "code"), CONSTRAINT "fk_ _siteid" 	FOREIGN KEY ("siteid") 	REFERENCES "mshop_locale_site" ("id") 	ON UPDATE CASCADE 	ON DELETE CASCADE ) ENGINE=InnoDB CHARACTER SET=utf8; -- PostgreSQL: no table options

Locale tables
The tables in the locale domain are


 * mshop_locale_site : Each record in this table represents an own shop instance and all tables should reference the ID of this table to provide the multi-shop feature. The records in this table can build a forest of sites (many trees side by side).


 * mshop_locale_language : This table contains the official languages available as two letter ISO language codes and are referenced when records are language dependent.


 * mshop_locale_currency : The currency table contains the official currencies available as three letter ISO language codes and are referenced when records are currency dependent (everything that is related to a price falls into this category).


 * mshop_locale : Contains the language/currency combinations per site that the customer is able to choose from. Without an appropriate entry in this table no text and price with a different language/currency is shown.

Catalog index tables
The index tables in the catalog domain contains the flat representation of the products in combination with texts, prices, attributes and the categorization. They belong to the default index implementation for searching and sorting products with a high performance and are filled either by a cron job or after the editor changed anything product related in the administration interface. In the administration interface, the editor does not have to care about this as it's done in the background when he saves the product.

The columns storing the ID of other tables are linked with the original table directly (cross domain references). In combination with the foreign key constraints for referential integrity, this ensures that the records from the index are removed as soon a the original records where deleted.

Type tables
If records in a table can be of different types (e.g. items in the product table can be "articles", "selections" or "bundles") then the type definitions should be stored in a separate type table. Each list table does also have its own type table as the same record from another domain can be referenced twice with different types. An example for this is again the product domain where selection products are referenced with type "default" while suggested products are referenced with type "suggestion". The types of the list table are also different by domain.

A template for a type table is:

CREATE TABLE " _ (_ )_type" ( 	-- Unique ID 	"id" INTEGER NOT NULL AUTO_INCREMENT, -- PostgeSQL: SERIAL 	-- Site ID, references mshop_locale_site.id 	"siteid" INTEGER NOT NULL, 	-- Domain the type is for 	"domain" VARCHAR(32) NOT NULL, 	-- Unique code 	"code" VARCHAR(32) NOT NULL COLLATE utf8_bin, -- PostgreSQL: without collation 	-- Name of the type 	"label" VARCHAR(255) NOT NULL, 	-- Status (0=disabled, 1=enabled, other values for special purpose) 	"status" SMALLINT NOT NULL, 	-- Time stamp of creation 	"ctime" DATETIME NOT NULL, -- PostgeSQL: TIMESTAMP 	-- Time stamp of last modification 	"mtime" DATETIME NOT NULL, -- PostgeSQL: TIMESTAMP 	-- Editor who modified this entry at last 	"editor" VARCHAR(255) NOT NULL, CONSTRAINT "pk_ _id" 	PRIMARY KEY ("id"), CONSTRAINT "unq_ _sid_dom_code" 	UNIQUE ("siteid", "domain", "code"), CONSTRAINT "fk_ _siteid" 	FOREIGN KEY ("siteid") 	REFERENCES "mshop_locale_site" ("id") ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB CHARACTER SET = utf8; -- PostgreSQL: no table options

Note: Please keep in mind that identifiers must not be longer than 30 characters to work with Oracle.

List tables
When records from other domains should be referenced, list tables are the way to go. They also provide the possibility to associate more than one item and create a m:n relation between the main table and the table holding the content. Currently, those domains use list tables:


 * attribute
 * catalog
 * customer
 * media
 * product
 * service
 * text

A template for list tables is:

CREATE TABLE "<prefix_ _list" -- Unique ID 	"id" INTEGER NOT NULL AUTO_INCREMENT, -- PostgeSQL: SERIAL -- Site ID, references mshop_locale_site "siteid" INTEGER NOT NULL, -- Parent ID to ID of parent table (usually of type INTEGER) "parentid" INTEGER NOT NULL, -- List type ID, references list type table "typeid" INTEGER NOT NULL, -- Domain the referenced ID is from "domain" VARCHAR(32) NOT NULL, -- Referenced ID from other domain "refid" VARCHAR(32) NOT NULL, -- Valid from time stamp "start" DATETIME NOT NULL, -- PostgeSQL: TIMESTAMP -- Valid until time stamp "end" DATETIME DEFAULT NULL, -- PostgeSQL: TIMESTAMP -- Position of the reference "pos" INTEGER NOT NULL, -- Time stamp of creation "ctime" DATETIME NOT NULL, -- PostgeSQL: TIMESTAMP -- Time stamp of the last modification "mtime" DATETIME NOT NULL, -- PostgeSQL: TIMESTAMP -- Editor who modified this entry at last "editor" VARCHAR(255) NOT NULL, CONSTRAINT "pk_ _id" PRIMARY KEY ("id"), CONSTRAINT "unq_ _sid_pid_dm_rid_tid" UNIQUE ("siteid", "parentid", "domain", "refid", "typeid"), CONSTRAINT "fk_ _pid" FOREIGN KEY ("parentid") REFERENCES "mshop_ " ("id") ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT "fk_ _siteid" FOREIGN KEY ("siteid") REFERENCES "mshop_locale_site" ("id") ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT "fk_ _typeid" FOREIGN KEY ("typeid") REFERENCES "mshop_ _list_type" ("id") ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB CHARACTER SET=utf8; -- PostgreSQL: no table options

Note: Please keep in mind that identifiers must not be longer than 30 characters to work with Oracle.

<< Previous: Important conventions | Next: Items >>