Difference between revisions of "Project:Inventory system"

From London Hackspace Wiki
Jump to: navigation, search
(Ideas)
(Database schema)
Line 91: Line 91:
  
 
CREATE TABLE queue_items (
 
CREATE TABLE queue_items (
     id             INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+
     id                 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     queue     TINYINT UNSIGNED NOT NULL, -- queue.id
+
     queue           TINYINT UNSIGNED NOT NULL, -- queue.id
     item     MEDIUMINT UNSIGNED NOT NULL, -- item.id
+
     item         MEDIUMINT UNSIGNED NOT NULL, -- item.id
     resolved   BOOLEAN          DEFAULT NULL -- 0 = Ignored, 1 = Done, NULL = Pending
+
     resolved       BOOLEAN          DEFAULT NULL -- 0 = Ignored, 1 = Done, NULL = Pending
 +
    resolved_date TIMESTAMP
 
);
 
);
 
</pre>
 
</pre>

Revision as of 21:58, 8 August 2010

Inventory system


Members roberthl, ms7821
QR code


We'd like to have an inventory system for cataloguing the space's equipment and supplies.

For now we're going to see how the Inventory page evolves, and then look in to what enhancements we need to make - if any.

Ideas

Here are some ideas I've had for the project, would appreciate comments. --Robert 13:33, 8 August 2010 (UTC)

The primary goal is to produce a flexible system for people who have an interest in keeping the inventory system up to date and comprehensive, while at the same time making it so people who don't care can accomplish what they need to in as little time as possible.

Inventory Workflow 1.png
List
The application is centred around one list that contains all equipment, components, etc.
Item
The list is composed of items.
Categories
All items have one category.
For example
Component
Properties
Each item has properties common to all item types, and ones unique to its category.
For example (common)
Wiki page
Date acquired
Quantity
Identification description
Category
Location
For example (unique to "Components")
Reorder URL
Reorder Price
Queue
Items can be placed in to one or more queues, and can appear in queues multiple times (providing there is only one unresolved occurrence), to be processed at a later point.
For example
Reorder
Stock check
Disposal
Queue items
Linked to a regular item (extension of), but with the additional properties "Resolution" and "Resolution date" and attached to a queue.
Relation
Similar objects can be linked together, this can be resolved recursively up to a limit.
Log
All changes appear in a log with a change description, previous value, new value, user, and time.

Database schema

CREATE TABLE category (
    id         TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY_KEY,
    title VARCHAR(255)          NOT NULL
);

CREATE TABLE item (
    id          MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title    VARCHAR(255)          NOT NULL UNIQUE,
    category      TINYINT UNSIGNED NOT NULL -- category.id
);

CREATE TABLE item_properties (
    id                INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    item        MEDIUMINT UNSIGNED NOT NULL, -- item.id
    property     SMALLINT UNSIGNED NOT NULL, -- property.id
    `value`  VARCHAR(255)
);

CREATE TABLE item_links (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    item1 MEDIUMINT UNSIGNED NOT NULL, -- item.id
    item2 MEDIUMINT UNSIGNED NOT NULL  -- item.id
);

CREATE TABLE property (
    id           SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    category      TINYINT UNSIGNED NOT NULL DEFAULT 0, -- category.id -- IF 0 THEN common
    name     VARCHAR(255)          NOT NULL
);

-- Values which a given property may have.
CREATE TABLE property_values (
    id                INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    property     SMALLINT UNSIGNED NOT NULL, -- property.id
    `value`  VARCHAR(255)          NOT NULL
);

CREATE TABLE queue (
    id    TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255)     NOT NULL
);

-- Categories from which items may be a member of this queue.
CREATE TABLE queue_categories (
    id       SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    queue     TINYINT UNSIGNED NOT NULL, -- queue.id
    category  TINYINT UNSIGNED NOT NULL  -- category.id
);

CREATE TABLE queue_items (
    id                  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    queue           TINYINT UNSIGNED NOT NULL, -- queue.id
    item          MEDIUMINT UNSIGNED NOT NULL, -- item.id
    resolved        BOOLEAN          DEFAULT NULL -- 0 = Ignored, 1 = Done, NULL = Pending
    resolved_date TIMESTAMP
);