item

Description

Base of product and category.

The item constructs tree structure. Leaf is a product and node is category.

Because the item is one of primitive information, item deletion can break other table's reference. Instead of deleting item, it sets deleted flag to state column. (FIXME: category deletion has no problem. so it would be better to move to product.)

Definition

Column Type Constraint Description
shop TEXT PRIMARY KEY Shop ID. (shop.username)
id INTEGER Identifier. (1-origin)
type INTEGER NOT NULL Type of item.
  • 1: Category
  • 2: Product
parent_id INTEGER NOT NULL Parent category ID. If the value is 0, the item has no parent.
state INTEGER NOT NULL State of item.
  • 0: Deleted. Even invisible from administration interface.
  • 1: Just visible from administration interface.
  • 2: Also visible from customer interface.

Related tables

Query

CREATE TABLE item(
shop        TEXT,
id          INTEGER,
type        INTEGER   NOT NULL,
parent_id   INTEGER   NOT NULL,
state       INTEGER   NOT NULL,
PRIMARY KEY (shop, id)
);

item_content

Description

Multilingualized name and description of item.

Definition

Column Type Constraint Description
shop TEXT PRIMARY KEY Shop ID. (shop.username)
item_id INTEGER Item ID. (item.id)
lang TEXT Language key.
name TEXT NOT NULL Name.
description TEXT NOT NULL Description.

Query

CREATE TABLE item_content(
shop        TEXT,
item_id     INTEGER,
lang        TEXT,
name        TEXT      NOT NULL,
description TEXT      NOT NULL,
PRIMARY KEY (shop, lang, item_id)
);