Unanswered: DB Design Suggestions Please for IT tracking
I am trying to design a database in Access 2007 that allows me to keep track of all that an IT consultant comes up against in his/her day to day activities (keep track of client specific data, their software licenses, inventory etc...).
What I need help on is how to design the db so that I can inventory a computer when a company purchases it (along all the specifics in hardware that it came with) but, then keep track of the lifecylce of that machine.
-A company buys a new computer on 2/12/99 and has P3 processor with 256MB of RAM, and 40 GB hard drive and is named Computer-A1 and assigned to Tim Smith. (this is entered into the database)
-On 12/1/2000 it is re-named to Computer-B1 and given Patty Wiles. (this too is recorded in the db)
-On 4/5/2002 the memory on this system is upgraded to 512, re-named Computer-C5 and given to Brad Stokes. (this too is entered into the db)
-On 1/20/2003 the machine is retired and donated to charity.
Now, what I am trying to accomplish (and learn) is how you would design the database so that I could capture all this data and produce a report that would:
Show the entire history of that machine
Show all machines belonging to CompanyA and their present status as of the present date.
An excellent place to start would be to follow the link in my signature titled Access Design Tips. When you see the web site, follow the top link and read all about normalizing a database. This will be more than worth the time it will take to read and understand normalization.
You have a great start but it is a very big project. It looks like you have already broken it into smaller pieces, wonderful. I'm still working on the whole design aspect of development myself. So far, I have had the best luck coming up with a basic workflow through the program then trying to thing of 1/2 dozen outlier cases and letting what I come up with there drive my table design. If you work all the way through the Normalization process, that is very valuable in getting the right tables in place.
If some of the more Veteran developers have a scheme that works for the them, I would find it incredibly useful.
Startmann, great ideas there. And to follow your suggestion, one of the main things I do is work to get all the output requirements of the desired system identified, and how the client wants to see this output. When you know where the system needs to end up, it makes getting there a lot easier to plan and organize. In other words, if you have a good, but can be rough, design of all your reports, the rest of the system is easier to design, and I find the normalization process is even easier too.
Have you had a go at doing thsi yourself yet? I suggest you take a pen and paper (technology rocks, eh?) and just sketch out an ER (Entity Relationship) diagram showing each of your entities (e.g. a computer, a person, etc) and how they relate to eachother (e.g. one person can have how many computers?)
In terms of storing historic information then it will always be worthwhile thinking early on about how you identify who-had-what-when (here's a clue: a date(time) value against a record).
Post back what you've come up with so far and people will be able to comment on what you have got right and what may need a little bit more thought.