| Inventory system
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.
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.
- The application is centred around one list that contains all equipment, components, etc.
- The list is composed of items.
- All items have one category.
- For example
- Each item has properties common to all item types, and ones unique to its category.
- For example (common)
- Wiki page
- Date acquired
- Identification description
- For example (unique to "Components")
- Reorder URL
- Reorder Price
- 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
- Stock check
- Queue items
- Linked to a regular item (extension of), but with the additional properties "Resolution" and "Resolution date" and attached to a queue.
- Similar objects can be linked together, this can be resolved recursively up to a limit.
- All changes appear in a log with a change description, previous value, new value, user, and time.
CREATE TABLE category ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY_KEY, title VARCHAR(255) ); 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 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 ); 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 queue ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) ); -- 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 ); -- items in a queue. 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 );