home inventory app

Info

for a year i worked at pathguide, they sell warehouse management software

to prepare for the interview i drafted this home inventory db

when working at xerox i had an hp 720, cool little clamshell pocket laptop with windows ce

i had some type of access type software on it and created an inventory tracker for my van

always wanted to make a simple inventory tracker for home, someday i'll finish this app on various formats

eventually the one i'd actually use would probably be a cloud api with an android app, possibly barcode labels and use the phone to scan them

the simpler the better, i don't have that much stuff, it's under renters insurance and i take pictures of everything so it's ok but it would be nice just for curiousity and to save time if my stuff is organzied better or i can't find something

typically i load up my stone glacier backpack, and just transfer the contents to the bicycle if i'm bike packing but the gear doesn't change much, that's why i got the stone glacier backpack it carries all the heavy comfortable gear easily so my bikepacking and hiking gear are the same

i do have an ultra light set too, for if i ever get to do the entire pct, that is all dyneema backpack, tarp, ul quilt etc. but i rarely use it because it's just not as comfortable as the big comfy camping gear, if i'm going on a weekend trip there's not really a need to be a gram weenie, your body can adjust to carrying weight

  • create db and tables
  • add seed data
  • general queries an app would use
  • add images, weight and price to stuff
  • add dimensions to boxes

todo: web ui

  • create a flask crud version with sqlalchemy
  • create a django crud version with app generator
  • create a razor pages crud version
  • create an mvc crud version

setup db

--use sandbox
create DATABASE sandbox
use sandbox

create storage locations

-- warehouses at my house are garage and closet
CREATE TABLE warehouses ( id INTEGER PRIMARY KEY, location VARCHAR(25) );
--drop table warehouses
INSERT INTO warehouses VALUES (1, 'garage'), (2, 'closet')
insert into warehouses values (3, 'bedroom')
insert into warehouses values (4, 'shed')
insert into warehouses values (5, 'job box'),(6, 'locking cabinet')
SELECT * FROM warehouses;

setup boxes with labels

CREATE TABLE boxes
(
    id INTEGER PRIMARY KEY,
    label VARCHAR(25),
    warehouse INTEGER,
    FOREIGN KEY(warehouse) REFERENCES warehouses(id) 
);


INSERT INTO boxes VALUES (1, 'electronic gear',2), (2, 'camping gear', 1), (3, 'music gear',1)
insert into boxes values (4, 'music gear', 1)
-- this is silly but i want to put stuff in a no box vs null box for experimentation
insert into boxes values (5, 'camping food', 3)
insert into boxes values (4, 'unboxed or in use')
--delete from boxes
--update boxes set warehouse = null where label like 'unbox%'
--update boxes set warehouse = 2 where label like 'mus%' 
SELECT * FROM boxes; 

stuff with names

CREATE TABLE stuff
(
    id INTEGER PRIMARY KEY,
    name VARCHAR(25),
    box INTEGER,
    FOREIGN KEY(box) REFERENCES boxes(id)
)

INSERT INTO stuff VALUES (1, 'alienware13',1), (2, 'pixelbook', 1), (3, 'alienware17', 1) 
insert into stuff values (4, 'flash drives',1), (5, 'sg backpack',1)
insert into stuff values (6, 'moog',4)
insert into stuff values (7, 'particles','')
--delete  from stuff where id =  7
SELECT * FROM stuff

categories for stuff

-- sp_help warehouses

-- select * from Information_schema.Columns
-- where table_name = 'box'

-- many to many categories for stuff

create TABLE categories (
    category_id INTEGER PRIMARY KEY,
    category VARCHAR(25)
)

INSERT INTO categories VALUES (1, 'edc'), (2, 'adventures'), (3, 'music'), (4, 'electronics'), (5, 'bags')

select * from categories

-- m2m categories on stuff

CREATE TABLE stuff_category (
    stuff_id INTEGER,
    category_id INTEGER,
    CONSTRAINT stuff_cat_pk PRIMARY KEY (stuff_id, category_id),
    CONSTRAINT fk_stuff
        FOREIGN KEY (stuff_id) REFERENCES stuff (id),
    CONSTRAINT fk_category
        FOREIGN KEY (category_id) REFERENCES categories (category_id)
)

INSERT INTO stuff_category VALUES (1,1), (1,2)

select * from stuff_category

general queries

--query acronym SFWGHO select from where group having order 

how much stuff is in each box?

select count(stuff.name) as "things in"
        ,boxes.label as "box labeled"
from stuff
join boxes
on stuff.box = boxes.id
group by boxes.label

show empty boxes?

select boxes.label as 'empty box'
    ,stuff.name as ' missing stuff'
from boxes

total boxes?

select count(*) from boxes

how many boxes are in per warehouse?

select count(boxes.label) as "box count"
    ,warehouses.location
from boxes
join warehouses
on boxes.warehouse = warehouses.id
group by location
order by "box count" desc

show stuff that is not in a box?

--select * from stuff
--trick question it requires box is a foreign key

how much stuff is in a the garage?

select count(stuff.name) as "number of things"
        ,warehouses.location
from warehouses
join boxes on boxes.warehouse = warehouses.id
join stuff on stuff.box = boxes.id
where warehouses.location = 'garage'
group by warehouses.location

which warehouse has the most stuff?

select count(stuff.name) as 'number of stuff', 
    warehouses.location
from warehouses
join boxes on boxes.warehouse = warehouses.id
join stuff on stuff.box = boxes.id
group by warehouses.location

Comments