1,143
edits
Project:Inventory system: Difference between revisions
From London Hackspace Wiki
→Database schema
(→Ideas) |
|||
| Line 43: | Line 43: | ||
<pre> | <pre> | ||
CREATE TABLE category ( | CREATE TABLE category ( | ||
id | id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY_KEY, | ||
title VARCHAR(255) NOT NULL | title VARCHAR(255) NOT NULL | ||
parent TINYINT UNSIGNED | |||
); | ); | ||
| Line 68: | Line 69: | ||
CREATE TABLE property ( | CREATE TABLE property ( | ||
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, | id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, | ||
name VARCHAR(255) NOT NULL | 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 ( | CREATE TABLE property_values ( | ||
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, | id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, | ||
| Line 84: | Line 89: | ||
); | ); | ||
CREATE TABLE queue_categories ( | CREATE TABLE queue_categories ( | ||
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, | id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, | ||
| Line 99: | Line 103: | ||
); | ); | ||
</pre> | </pre> | ||
<pre> | |||
INSERT INTO category SET title="Electronics"; | |||
INSERT INTO category SET title="Components",parent=LAST_INSERT_ID(); | |||
INSERT INTO category SET title="Integrated Circuits",parent=LAST_INSERT_ID(); | |||
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 title="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 title="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="74HC595 8-Bit Shift Register",category=3; | |||
INSERT INTO item_properties SET item=1,property=2,value=nnnnn; | |||
