Project:Inventory system

Inventory system
Members	roberthl, ms7821
QR code

Status: Database schema designed, object-API and basic front-end in progress.

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.

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

Owner (defaults to "Hackspace Foundation")

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,
    parent      TINYINT UNSIGNED
);

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,
    name VARCHAR(255)          NOT NULL
);

CREATE TABLE property_categories (
    id       SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    property  TINYINT UNSIGNED NOT NULL, -- property.id
    category  TINYINT UNSIGNED NOT NULL  -- category.id
);

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
);

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  DATETIME          DEFAULT NULL
);

CREATE TABLE user (
    id                INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email    VARCHAR(320)          NOT NULL UNIQUE,
    password VARCHAR(512)         NOT NULL
);

Sample data

INSERT INTO category SET title="Electronics";
INSERT INTO category SET title="Components",parent=LAST_INSERT_ID();
INSERT INTO category SET title="Resistors",parent=2;
INSERT INTO category SET title="Light Emitting Diodes",parent=2;
INSERT INTO category SET title="Integrated Circuits",parent=2;
INSERT INTO category SET title="Mechanical";
INSERT INTO category SET title="Components",parent=LAST_INSERT_ID();
INSERT INTO category SET title="Materials";

INSERT INTO queue SET title="Restock";
INSERT INTO queue_categories SET queue=1,category=2;
INSERT INTO queue_categories SET queue=1,category=5;
INSERT INTO queue_categories SET queue=1,category=6;

INSERT INTO property SET name="Restock URL";
INSERT INTO property_categories SET property=1,category=2;
INSERT INTO property_categories SET property=1,category=5;
INSERT INTO property_categories SET property=1,category=6;
INSERT INTO property SET name="Farnell Order Code";
INSERT INTO property_categories SET property=2,category=2;
INSERT INTO property_categories SET property=2,category=5;
INSERT INTO property_categories SET property=2,category=6;

INSERT INTO item SET title="10Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="12Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="15Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="18Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="22Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="27Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="33Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="39Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="47Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="56Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="68Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="82Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="100Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="120Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="150Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="180Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="220Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="270Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="330Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="390Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="470Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="560Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="680Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="820Ω 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="1.0kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="1.2kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="1.5kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="1.8kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="2.2kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="2.7kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="3.3kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="3.9kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="4.7kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="5.6kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="6.8kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="8.2kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="10kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="12kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="15kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="18kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="22kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="27kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="33kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="39kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="47kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="56kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="68kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="82kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="100kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="120kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="150kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="180kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="220kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="270kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="330kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="390kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="470kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="560kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="680kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="820kΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="1.0MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="1.2MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="1.5MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="1.8MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="2.2MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="2.7MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="3.3MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="3.9MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="4.7MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="5.6MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="6.8MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="8.2MΩ 0.25W 1% Resistor",category=3;
INSERT INTO item SET title="5mm 20mA 2.1V Diffused Red LED",category=4;
INSERT INTO item SET title="5mm 20mA 2.1V Diffused Green LED",category=4;
INSERT INTO item SET title="5mm 20mA 2.1V Diffused Yellow LED",category=4;
INSERT INTO item SET title="5mm 20mA 2.1V Clear Red LED",category=4;
INSERT INTO item SET title="5mm 20mA 2.1V Clear Blue LED",category=4;
INSERT INTO item SET title="5mm 20mA 2.1V Clear Green LED",category=4;
INSERT INTO item SET title="5mm 20mA 2.1V Clear White LED",category=4;
INSERT INTO item SET title="74HC595 8-Bit Shift Register",category=5;
INSERT INTO item_properties SET item=1,property=2,value="1750218";