Senior Project Demo'd and Documentation posted

I visited Dr. Griggs this afternoon and demonstrated the database application for final submittal. This benchmark concludes the work done for my senior project credit.

We went over the issues that I was confronted with throughout the duration of the project, showed the database tables and constraints and also the working front-end of the system. We discussed the areas where the business rules of the organization for which this database was designed caused the design to deviate from a perfect textbook example of a database management system.

I handed in the final paperwork and also posted it on my site at the CSU's server http://129.65.91.72/enash/

I'll be looking to enhance the design and functionality with the information gleaned from BUS496 next quarter by making the SQL database accessible via a .asp or .php interface.

Finally got my close button!

What a pain in the neck! Hours spent on the silliest thing.
Issue: wanted to have a button that would automatically set the close date to getDate() when tech claims ticket is resolved.

Why not just allow user to do it? Because I don't want them cheating and saying their turnaround time was better than it is. I also wanted the tech not to be able to modify the field.

Solutions:

1. Views in SQL? No. Would never refresh after the initial form was opened in Access
2. Stored procedure? No...well probably, but I don't know enough about SQL to do it right. I got it to work in that Access would pull up the stored procedure's results but not plug the data where I wanted it. The only way I could think of would be creating a parameter and passing the row number to modify only one cell, and then it went pear shaped.
   
3. Fix: created a macro, using the Access expression Now() and told it to fill in the close_date field and now I get a matching format to the dateTime values (I already set my SQL table to automatically use getDate() whenever a new record is created for the open_date) in the open_date.

Also, I added some new reports that look better than the user just looking at the raw query results.

In addition I got a reasonably advance query to work showing number of tickets assigned to the tech, and his/her average turn around. Somewhat tricky because I didn't want the numbers skewed by unassigned tickets, or tickets that weren't closed yet.

Business rules don't allow good database model

I did get the merge done between the ticket database and the software licensing database, but I found that the company has put a roadblock in my design plans. To track the software properly I need a unique asset tag (read primary key) on each machine and the corresponding database table would in turn have all the machine stats. However, no such infrastructure exists. To create it--getting to 5 remote offices, inventorying/tagging over 125 machines isn’t possible under my current demands at work (read they wouldn’t pay me to do this project, when they have other higher priority projects they have me on). I think I’m going to create the table anyway for future versions of the database, but for now, the machine will have to just loosely be identified (e.g. marketing Dell laptop, PowerEdge 2800 server, receptionist’s Compaq…).

Still having issues with the automatic close date for the ticket, for now I may have to allow the user to type in the data (only managers/IT staff will have access to it anyway).

Merging databases, triggers? Stored procedures? Growl

Well, now that the main database is up, normalized and working I thought I'd get ambitious and merge another database I'm doing for work. It's a software license tracking database, it would have a common table with this one anyway, "employee" and the current "employee_id" would be a nice foreign key to the IT Ticket's "employee" table. Really not a problem but I'll just have to hand code the data in again as that one was made just in Access. It would also let me migrate into the ticket database asset tags, and IP numbers. User could then further identify problem areas beyond location and add machine name/IP/asset tag....of course they'll NEVER bother to gather this data, so I'll have to allow null fields in the ticket.

The real issues I'm hitting is I want to do more that the Access front end is allowing me to do. And I don't know how to do VB.NET well enough to rework the front end by hand coding.

If I can get some triggers figured out, it'll be ok. I want to change the status to closed and have the database automatically input the datetime in the "date_closed" field.

Which brings up some of my other ideas, now that I've been working with a working BETA model the last couple weeks. I'm going to want to do a priority "low" "medium" "urgent," and also put a status "unassigned," "assigned," "closed" etc. Right now it just presumes the ticket is closed if there is a closed date, but maybe the queries will be be easier to program (date math is a hassle) if I just validate the "status" field.