| Author |
Topic |
|
OfMartin
Starting Member
11 Posts |
 Posted - 2005-04-12 : 17:20:25
|
| I'm writing SQL to keep track of damage or theft of equipment transactions in my database. I've created an availability bit field to keep track of whether equipment exists in inventory for the "Equipment" table. If the equipment is unavailable I'm adding a record in the "Loss Report" table. From there, I must update the equipment table with barcode. My question is how am I supposed to update the equipment table when I do not have "UnitsInStock" as a attribute of equipment b/c every equipment has a unique in house barcode. |
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2005-04-12 : 17:40:28
|
| you say that you've created an availability bit field to keep track of whether equipment exists in inventory for the "Equipment" table. So I supose that once the equipment is adquired it is added to the equipment table with its unique barcode and availability field set to 1 (available). If the equipment becomes unavailable you still have the barcode, why wouldn't you have it? (i think I missed something here)I would create a SP to update the availability field in the equipment table (to 0) and add a record to the loss report table.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.A candle loses nothing by lighting another candle |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-04-12 : 20:44:22
|
| It sounds like the bar code uniquely identifies each individual piece of inventory. Does it also identify the type of equipment (e.g., Tent vs Slepping Bag)? If so, you could break out the data into two tables; one to list the models or types of equipment, another to identify the specific item of that type. The item table would be a child of the model table (Foreign Key relationship). The availabilty would be an attribute of the model table (Do we have any tents in stock?) and the full bar code would identify the individual tents.HTH=================================================================In order to improve the mind, we ought less to learn than to contemplate.-Rene Descartes, philosopher and mathematician (1596-1650) |
|
|
|
OfMartin
Starting Member
11 Posts |
Posted - 2005-04-12 : 23:49:28
|
| So in writing SQL for tracking damaged or stolen equipment, I have already created a super/subtype relationship between 'EquipmentType' and my 3 tables of equipment(Computers, Printers, & Monitors). They all have barcode as a the primary key. So speaking of those extra tables, would I ever need to make a SP that would involve those tables and 'EquipmentType'? |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-04-13 : 00:13:08
|
quote:
So speaking of those extra tables, would I ever need to make a SP that would involve those tables and 'EquipmentType'?
??? How would we know this? It's possible, but it depends on the needs of the business. That's one we can't really answer without being there. We can guess though.  My guess would be that eventually you're going to want some kind of report requiring information from all tables and the type. It would also be nice to have on whatever tool you use to barcode and track all this stuff. It would be a nightmare to not have categories if you're keeping track of a few hundred thousand or even million parts.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
OfMartin
Starting Member
11 Posts |
Posted - 2005-04-13 : 10:43:13
|
| The needs of the business is to track the routine transaction of damaged or theft of equipment. They need a Windows-based form that would already include adding a record in the 'Loss Report' & updating 'Equipment' after. I've created a form already that allows the user to input fields for the 'Loss Report' in order to add a record(a couple of labeled textboxes for fields with an Add button to insert the values into the listview property to display the adding of Loss Report). Then, I have a part on the form that gives the user the ability to update 'Equipment' by querying for the specific barcode that matches from the added 'LossReport' and existing 'Equipment' in order to set the availability to '0' since the equipment is lost and needs its status to be changed to nonexistent in the 'Equipment' table.(2 labeled textboxes for barcode and availability with a Search button that would query the database for the specific equipment and display it in a listview property on my form). I am really stuck with the logic guys, would I need something extra on my form or need to write some extra SQL that would involve the 'Equipment Type' table and my 3 tables of categorical equipment(Printers, Computers, Monitors) if I'm tracking damaged or theft of equipment?Any help would be greatly appreciated,OfMartin890@yahoo.com |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2005-04-13 : 11:58:18
|
You almost have it. But the way I see it you are letting the user in charge of the data integrity. By business rule you will create a loss report when you find out that the equipment is missing then these two things should go by the hand. How are you validating that when a user creates a loss report he also updates the equipment table???Here is how I would do it:I would have a simple form with maybe just one text box where the user can enter the barcode and a button to update the equipment as not available anymore.the button will execute a SP (spEquipmentLost) that will recive the barcode. the sp will validate that an available equipment with that code exists and then will update the equipment table and insert a new record to the loss report table (barcode, reportDate, user). The SP will make the process more efficient because you will have only one round trip to accomplish all three tasks and also it will only have to be compiled and optimized the first time you run it.I would also have a loss report detail table (like a log) so users can add as many comments as it is necesary. This table would be updated from another screen after the equipment table has been updated and the loss report record has been created. quote:
I am really stuck with the logic guys, would I need something extra on my form or need to write some extra SQL that would involve the 'Equipment Type' table and my 3 tables of categorical equipment(Printers, Computers, Monitors) if I'm tracking damaged or theft of equipment?
you said that you have all the tables related... I think you can accomplish this task with some views.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.A candle loses nothing by lighting another candle |
|
|
|
OfMartin
Starting Member
11 Posts |
Posted - 2005-04-13 : 14:22:02
|
| That makes complete logical sense to make that SP. Not only does it makes sense, it will work. The only thing I'm not clear on your solution is for the use of the 2nd form. quote: "I would also have a loss report detail table (like a log) so users can add as many comments as it is necesary. This table would be updated from another screen after the equipment table has been updated and the loss report record has been created."As the user would know what barcode to input to update a specific equipment, he would not have access on a form to input Loss Report fields such as 'LossDate' and 'Reason'. Is this why you would create the 2nd form you were talking about? Maybe I'm missing something... |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2005-04-13 : 16:13:49
|
quote:
Originally posted by OfMartin
As the user would know what barcode to input to update a specific equipment, he would not have access on a form to input Loss Report fields such as 'LossDate' and 'Reason'. Is this why you would create the 2nd form you were talking about? Maybe I'm missing something...
The reason for this second table is that I think sometimes things get lost, stolen, moved, send for repair, etc. but the user doesn't necesarilly knows the reason why it was unavailable at a certain moment. In the other hand the equipment could be found or returned or never returned at all. All of this things, in my opinion, should be in a detail table not because the first form has only the barcode field (because that could be modified) but because maybe the user has no comment to make the day s/he finds out that the equipment was lost but days after some other user or the same user finds out why the equipment is unavailable and some other days there are new findings (like it was returned) and I guess you would like to keep track of all of these findings whish can be from 0 to many. So if you follow the rules of normalization these fields (updateUser, updateDate, comment) should be apart with the barcode or lossReport key (if different) to keep tables in the 3nf..*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.A candle loses nothing by lighting another candle |
|
|
|
OfMartin
Starting Member
11 Posts |
Posted - 2005-04-13 : 17:23:40
|
| Thanks a lot, tuenty. Anytime you need some help, here's my e-mail.OfMartin890@yahoo.comThanks again. |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2005-04-13 : 17:27:38
|
 .*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.A candle loses nothing by lighting another candle |
|
|
|
|
Updating the Equipment Table