Sunday, February 27, 2005

Access -> SQL, Forms, Reports, Normalization

It's been a productive week, after resolving the SQL questions, I rebuilt all the tables that were in Access into SQL (currently 6 tables).

After looking at the tables for normalization issues, and not liking some of the layout, I took out a ticket details table (because SQL text 16 will allow massive amount of text). Now the design will be for a continuous text field for initial problem entry and subsequent tech update entries. This makes the layout nicer, but another reason for the change was that multiple entries into a ticket caused me troubles on the reporting side (multiple rows shown for only on ticket number due to the one-many relationship with ticket and tech update entries).

I'm still using Access .adp file to access the SQL, and have customized some of the queries in SQL Analyzer, some in Access, I had to recreate the forms, and I'm doing customized navigation menus.

With the core infrastructure in place and stable now, I'm going to be looking at restricting editing per logins, creating a more stand alone (as in not opening full Access) look to the app, create a master form for all data entry, query, reporting functions.

Sunday, February 20, 2005

SQL issue resolved + updates

SQL autonumber issue was ridiculously simple when using the enterprise manager GUI to create the table (int/identity = yes/identity seed =1/identity increment = 1); hand coding it in a text file to create tables in the query analyzer wasn't working so hot. It looks like I'll use the GUI of the Enterprise Manager for the continuation of the project. So the next steps are to export my tables from Access or re-create them in SQL. SQL or an Oracle as the database is the mature way to design this project, and Access was going to be the easy way out. Plus I want this project to be able to grow, and creating it in a enterprise level database will allow for that; opening the door for .asp/.php or VB.NET programming.

I've signed up for BUS496 next quarter where I can get some .asp:database experience, but won't be able to utilize that skill for the graded portion of this project.

Sunday, February 13, 2005

SQL pain

Been having issues with SQL. Proper foreign key constraints and autonumbering getting in my way. I have fallen back to Access just to get a working model and use the GUI to make headway.
I worked with Slackware Apache with MySQL on another project to see if I could go that route too, but learning curve is too steep to do a custom app with. Installing someone else's code is fine, but way to deep for me to make work on this project.
Intentions are to clean up (normalize) what I have now using Access and get some good reporting and stable interface, then get back to getting SQL to work in the background.

Wednesday, February 02, 2005

Systems Analysis

As my Systems Analysis class progresses, I've been working with Visio and have a pretty good DFD of the proposed system. As I'm not sure what the end-user interface will be as yet, I can't complete it, but creating a DFD and thinking about the processes has helped me to visualize the component parts and the players in the system.