[Rails] Inventory Controller

["Luis G. Gómez"]

Hello friends...

What do you think of this?

I have never done any serious inventory management tools so it occured
to me I could practice my rails skills creating a generic inventory
controller. You may have experience on this subject and that's why I
wanted to share my approach so you can tear it appart, critique it and
help me learn something new.

I currently have no use for this but I'm sure in the future it'll prove
to be usefull. Of, course this controller would be available to all
rails users or even made available in tutorial form.

My first draft goes like this:

Tables
=====

items
------
id
properties

locations
---------
id
location_x
location_y
location_z
name
parent_id

categories
----------
id
name
parent_id

items_locations
---------------
item_id
location_id
quantity

categories_items
----------------
item_id
category_id

[[item]]_properties
------------------
item_id
.....

The idea behind these tables is to have an items in the "items" table
that store their properties in a separate table dedicated solely to
storing properties for it. I do this so I can have any number of
different properties for different items (or SKUs). Locations store
coordinates as well as quantity information. I'm visualizing locations
as containers for x number of items. Locations can be nested so you can
have many nested under one which identifies a warehouse for example.
Categories are there simply to aid in organizing items but are not required.

Now, I'd like the user of the controller to be able to drop a copy into
their app and be able to use it with the least number of modifications
possible. Would you include setup methods in the controller? Meaning,
inventory#setup could be called to create the necessary tables (if not
found).

When creating a new item the controller should promt the user to asign
properties for it (i.e: fields to its properties table) and each
property's type.

One should be able to perform at least the following actions (please
excuse the way I describe it here as I'm not sure if it's correct but
just want to illustrate my point):


ITEMS
---------
#insert a new item to the items table and create a table for the
#items properties called item_id+"_properties".
inventory.item.new ()

#remove item and its properties table
inventory.item.destroy ()

#add an item to inventory
inventory.item.add (quantity[,location])

#delete an item from inventory
inventory.item.delete (quantity[,location])

#add a property (field) to the item's property table
inventory.item.property.add (name[,type])

#modify a property (field) in the item's property table
inventory.item.property.modify (new_name[,new_type])

#remove a property (field) from the item's property table
inventory.item.property.destroy ()

#return total number of items
inventory.count ([item_id])

#CATEGORIES
--------------
#add a category to the categories table
inventory.category.new (parent)

#remove a category to the categories table
inventory.category.destroy (destroy_children=false)

#modify a category in the categories table
inventory.category.modify (new_name[,new_parent])

#assign an item to a category
inventory.category.asign (item_id)

#remove an item to a category
inventory.category.remove (item_id)

#return number of items in this category
inventory.category.count (item_id)

#LOCATIONS
--------------
#create a location
inventory.location.new (name,x,y,z[,parent])

#destroy a location. If subtract children==true its children are
# "moved" to the location's parent
inventory.location.destroy (substract_children=false)

#set a locations name
inventory.location.name ()

#set a locations x coordinate
inventory.location.x (value)

#set a locations y coordinate
inventory.location.y (value)

#set a locations z coordinate
inventory.location.z (value)

#set a locations parent
inventory.location.parent ()

#asign an item to the location
inventory.location.asign (item_id[,quantity])

#remove an item from the location
inventory.location.remove (item_id[,quantity])

#move items from one location to another
inventory.location.move (from_id,to_id,item_id[,quantity])

#return number of items in this location
inventory.location.count (item_id)

#END


I'm sure there's more functionality that can be included. I'm new to OO,
ruby and rails so any ideas or opinions are appreciated.

What do you think?

Thanks to all...

LG