I am trying to write a inventory web application with RoR so that I can
keep track of hardware and software purchased by the company. I would
also like to be able to associate products we buy with users and
departments. I would like it if you could enter in products (since we
purchase many of the same) into a product list.
Then you could add a user or department and check off which products he
has and then maybe some AJAX for cool effects for entering the serial
numbers or additional information. I am not sure if it is the best way
but I wanted to add a new column to the “users” table everytime a
product was entered and then that would be available to select. I would
like to know how I would have to have this project designed so that it
can work within the MVC framework and what tables you think I might
need.
~Rahul
Hi Rahul,
From your description I don’t think that adding a column to the user table
would be wise. This would limit the number of products that you could
add
to what ever the database can handle as the number of fields.
Rather if I have understood you correctly you could use a table
structure
like:
Products
id
name
user_id
department_id
…
Users
id
…
Departments
id
…
Then your classes could be something along the lines of
Product
belongs_to :user
belongs_to :department
User
has_many :products
Department
has_many :products
You could then add products into a list, and associate them with either
a
department, a user or both.
Cheers
On Tue, 2006-03-14 at 20:37 -0600, Rahul M. wrote:
like to know how I would have to have this project designed so that it
can work within the MVC framework and what tables you think I might need.
~Rahul
Adding columns is not just a bad idea … it is an evil idea that will
make the product unusable in the long term.
You will need at least these tables:
ITEMS: list of available items and generic sorts of information about
them
ITEM_ATTRIBUTES: name -> value attribute pairs for each item
USERS: list of users known to the system
USER_ATTRIBUTES: name -> value attribute pairs for each user
DEPARTMENTS: list of departments known to the system
INVENTORY: list of all instances of inventory items and where to find
them (user, department, installed in machine, etc)
INVENTORY_ATTRIBUTES: name -> value attribute pairs for each specific
instance of a product
If you want to track inventory history, which is required in some cases,
you may need to be a bit creative around the INVENTORY table.
Use the name-attribute pairs unless you find that you are always keying
in the same type of information - serial number for example. Rows
(data) can be maintained by the users, but columns (metadata) require a
programmer’s continual input.
Use surrogate keys (means something to the DBMS, but has no business
meaning) for robustness and long term maintainability.
I hope this helps.