Looks good, but I have a few questions. Some might be answered in the code, but I couldn't find some of them:
1. I assume "Location" is something like a bin. If so, it should probably have its own table, and Items.Location would be a FK. I say this since we'll definitely have multiple items in the same bin and we might want to add fields to "Locations" themselves (description of the bin for instance, labels on the bin, etc.).
- Location is a varchar field so we can be flexible in the conventions, as they will likely vary from room to room. aceat64 01:53, 16 July 2011 (CDT)
2. Should we have a way to mark "Item.Qty" as approximate. There's no way we're actually counting those surface mount items and I'm guessing we'll be doing approximate weight quantification :).
- Information like this should be put in the notes field. aceat64 01:53, 16 July 2011 (CDT)
3. Items might have values as well. I think the values should have numbers and units. I think if we're relying on "Items.Notes" to for instance search for a 2.7K Ohm resistor might be a little messy. Was it entered as 2.7K Ohm, 2700 Ohm, 2,700 Ohm, or one of the previous values with a fancy Ω character? Some prefixes might get messy as well (micro, people confusing M with m, etc.). I think it'd be cool to normalize all units on the back end and let people specify values in the UI with units. For instance, the resistor would be normalized in the database (integer value/mantissa for instance), but the UI would let you specify 2700/2.7K/0.0027M Ohms. The data access layer would automatically convert it to the normalized version server side.
- This will be handled in the name, e.g. "4.7kOhm Resistor". Also, the item will be in the "Resistors" category. aceat64 01:53, 16 July 2011 (CDT)
4. Is there a way to put comments on "Verifications"? Are you going to put a usage history as well?
- Good idea, I'll add a comment field for each verification. aceat64 01:53, 16 July 2011 (CDT)
5. What is the point of "Categories.Lft" and "Categories.Rght"? Are you using it to make a doubly-linked list for some reason?
- These are required for the MPTT tree logic, they are updated automagically and are not shown to the end user. See the following sources for more info: http://en.wikipedia.org/wiki/MPTT and http://book.cakephp.org/view/1339/Tree aceat64 01:53, 16 July 2011 (CDT)
6. Just curious what other "Buildings" you anticipate? If we ever did add another building, you could always run a script to insert all the FK's into existing "Rooms" rows. So if "Buildings" is more work for any reason (and if there is no need now), it could be omitted.
- It's possible that items may be stored off-premise in the future and/or that the space will expand. You are correct, this table could be omitted for now, and added later relatively easily. As an aside, adding the information on the buildings table to this wiki required more work than adding it to the project, removing it would likewise be fairly trivial. aceat64 01:53, 16 July 2011 (CDT)
If you want, I'll do a cheesy little diagram of this DB showing FK's etc.