The Grex Inventory Database

Notes on the Inventory Database

The basic structure of the database is all in two tables, called items and yearlies. Each item we own has exactly one row in items and one in yearlies for each year we've owned it. (Back to 2000, the first year we had to pay taxes.)

The bulk of the information about an item goes into the items table, and only that which changes from year to year goes into yearlies. Currently, that's only 3 columns: value, status, and parent. (Parent is the item which an item is inside; for instance, if a chassis conatins several cards, the chassis is listed as the parent of the cards. Keeping track of parents is meant to help us find things in the Pumpkin. Obviously, lots of items don't have a parent.)

What to do

I need staff's help with four things:

1. Updating the status of items as they go into and out of service,
2. Adding items to the database as we acquire them,
3. Marking items as released when we get rid of them, and
4. Assigning a status and a value to each item once a year (between December and February).

To update the status of an item:

1. Click on the "Staff Login" button and enter your username and password.
2. Click on the "Items" button.
3. Find the item whose status has changed. You can restrict which items appear by changing the criteria at the bottom of the page.
4. Click on the item number.
5. At the bottom of the resulting page, choose the appropriate status from the drop-down list.
6. Click on the "Save changes to this item" button.

To add a new item:

1. Click on the "Staff Login" button and enter your username and password.
2. Click on the "Items" button.
3. At the top of the page, click on "click here to add a new item".
4. The resulting Perl script (edititem.cgi) will retrieve the next available item number for you and put it on the screen. Be sure this number matches the number you put on the sticker that goes on the actual item.
5. Fill in the fields to the best of your ability. Don't worry about the "Year it was new" and "Price when new" fields if we obtained the item used.
6. Click "Add this item" to add the item.
7. If you want to add another item, click "Add a new item" and go to step 4.

I don't like deleting things from databases, since SQL makes it easy to have them still be there but not appear unless you specifically want to see them. So if we get rid of an item, don't delete it from the database. Just mark it as released. That will keep it from appearing on the items page and in the report, but will allow archival searches. (For instance, we will still be able to generate the PPT statement from a previous year.)

To mark an item as released:

1. Click on the "Staff Login" button and enter your username and password.
2. Click on the "Items" button.
3. Find the item you want to release and click on its number. You can set the criteria at the bottom of the page to make it easier to find the item.
4. You should see a screen with edit boxes and drop down lists. If not, you may not have done steps 1 and 2 in the right order. (People who are not logged in see a different page when they click on an item than people who are.) Go back to step 2.
5. Enter the current year in the "Year we got rid of it" field.
6. Click "Save changes to this item" to update the database.

Each item needs a status for each year we own it. Most of the statuses are self-explanatory; the only thing that might cause confusion is the difference between "Spare", "Idle or Obsolete", and "Not useful to us".

• Spare items are things that we are saving in case something that's in use breaks. They can be inserted into the system with a minimum of work. I think anything that's very similar to something we're using has to count as spare. We pay just as much tax on spare items as on "in use" items.
• Idle or Obsolete items are things that we used to use but don't anymore. They can't be swapped into the system immediately. For instance, I asked about the ISDN Pipeline modem at a time when we were using a DSL connection. We are saving that modem in case we lose our current connection and have to fall back to ISDN. But it doesn't count as spare, since we would have to get a new phone line from Ameritech, and do a lot of work, to install it. Idle and Obsolete items are assessed at 50% of the rate that in use and spare items are taxed.
• Items which are not useful to us need to be things which were donated to Grex but we have never used and never plan on using. We pay no tax on these items.

Also, there is one special status called "Not yet acquired" for items which are acquired between the end of the year and when the database is rolled over for the next year (which happens after values have been entered.)

All in use, spare, and idle or obsolete items need to be assigned a value each year. The rest technically don't, since we don't have to pay taxes on them. But it might help get rid of items we don't want if we assign what we think are reasonable values to them. That way people can browse the database and if they see something they like, for a price they like, they might buy it.

To set statuses and values for all items:

1. Click on the "Staff Login" button and enter your username and password.
2. Click on the "Yearly Data" button. You'll see a screen of item types.
3. Choose an item type and click on it.
4. Now you'll see a page which shows all items of the type you selected. Each item has a big table of data from the items table and then a smaller table for each row it has in the yearlies table. The last yearly block is the current one, and includes edit boxes for value and parent and a drop-down list for status.
5. Fill in the statuses of all the items on the page, and assign values at least to the in use, spare, and obsolete items. If you have to skip an item, that's OK - you can find it again later.
6. Click "Submit Changes" at the bottom of the page to save the changes in the database.
7. Click "Return to the main yearlies page".
8. Repeat steps 3-7 for each item type.
9. Click on "unset" at the bottom of the yearlies page to see all the items that still need attention.

Much of the functionality of the system is available to anyone. Of course only authorized people are allowed to edit the database, so the scripts which allow editing require logging in. That's what the "Staff Login" button on the button bar is for.

If you're a member of the Grex "staff" group, you can log in using your regular Grex login and password. Other people (like me) who are allowed to edit the database have a row in the database's users table, containing a username and password. Passwords are stored encrypted, using the standard UNIX encryption algorithm. The login form allows you to change your password to anything you like.

When you log in, the Perl script writes a cookie back to your browser containing your login name, login time, and a certificate which it makes by combining the login and the time and then encrypting the result. Every time you go to a page, the certificate is checked for validity and the cookie is updated if it checks. Making the certificate time dependent makes it harder to forge. A login expires after one hour of inactivity.

After you log in the button in the upper left hand corner of the screen should change from "Staff Login" to "Logout", and you should see some extra buttons at the bottom of the list.

When a page queries the database, it uses a different MySQL login depending on whether the user is logged on or not. The login for logged-in users has permission to change data, but the login for other users can only execute select statements.

Miscellaneous

The statuses and parents which are reported on the items page are current, 2005 statuses. The values, however, are from 2004. This is a bit confusing, but was necessary since values aren't assigned until the end of the year but we want statuses to be as up-to-date as possible.

Note that items which don't belong to us must be reported on the personal property statement, and in theory the owners might be sent a bill for them.

There are some auxilliary tables, which you can view from the button bar on the left side of the screen. If you're logged in as a staff member, you can edit them too. They should mostly remain static, though we may need to add a new source or manufacturer from time to time. The itemtypes and statuses tables have "ordinal" columns - their only purpose is to allow the rows to always be read in a particular order.

We shouldn't need to edit anything in any way other than through this web interface. If there's something you want to do, but can't find a way to do it, please let me know.

That's all I can think of right now - let me know about anything that's confusing, and I will try to explain it on this page.

Mark Conger

July 17th, 2001