AnonymousX Posted July 23, 2008 Share Posted July 23, 2008 During the summer I work for a high school, assisting the computer technician. This summer he has requested that I create a database (using Access 2007), that acts as a computer inventory for all computers in the district. I've created several small databases in a college course, but am a little unsure as to what structure I should use. I've provided a list of the five tables and their fields. I believe that I may need to simply use the primary key as a foreign key in the other tables, but I could be mistaken. If somebody could just reassure or correct me, it would be much obliged. Thanks.Hardware(Computer ID?)ManufacturerDisk SpaceMemoryProcessorVideoDisplayUSB PortsMac AddressEthernetTracking(Computer ID?)UserRoomImageIP AddressSerial NumberAsset TagSoftware(Computer ID?)Operating SystemAdobe CS3 SuiteMicrosoft OfficeCADMaintenance(Computer ID?)Service DateReplacement DateTimes in ShopCurrently in ShopCurrent Info(Computer ID?)RoomPerformanceCondition Link to comment Share on other sites More sharing options...
aspnetguy Posted July 23, 2008 Share Posted July 23, 2008 I would rename Hardware to Computers and add the fields from software and Current Info to the Computers table.Maintenance has to be a seperate table as there will be a record for each time it is serviced (yes you ComputerID as the PK)I am not sure what Tracking will do. Is this a list of users on each computer? If so then this is also a sperate table as there qwill be multiple users per computer (again yes use ComputerID). Link to comment Share on other sites More sharing options...
AnonymousX Posted July 23, 2008 Author Share Posted July 23, 2008 Basically the logic behind the table was to break down the different ways the computers are managed in the district. The hardware table shows us what components are in the computer. If parts are too outdated, they get updated or replaced. The tracking information's main purpose is to monitor the computer's current location and user. This table could be integrated with the Current Info table. However, we intentionally didn't include this data to the hardware as it is subject to change multiple times per year in some cases. The software was also excluded because the list of programs/suites is much larger than what's included. All fields are Yes/No check boxes, so it seemed easier and less busy to have them in their own table. And then finally the Maintenance table you mentioned.I understand that I must use the Computer ID field throughout the database. Is there any specific relationship types that I need to set up when using the Computer ID as the key in multiple tables? Link to comment Share on other sites More sharing options...
justsomeguy Posted July 23, 2008 Share Posted July 23, 2008 Like aspnetguy said you'll want to have one main computer table that contains 1 record for each computer. The fields you want in that table are any fields that will also only have 1 record for each computer. The maintenance table might have several records for each computer, if a computer gets maintained 5 times then there will be 5 records for the one computer. But there isn't going to be more than 1 record for the software in a computer, or the hardware, or the current info. All of those tables can be grouped into one computer table. If you want to simplify the software table, instead of having one field for each piece of software, have a few fields like this:computerIDapplicationtypeThe records would be stuff like this: computerID application type--------------------------------------------------------------1 Windows XP Pro SP2 system1 Photoshop 7 app1 WinRAR utility1 MS Word 2003 app2 Windows ME system2 My Little Pony Extreme app2 AOL app2 MS Works app etc. That way you would have several records for each computer, however many pieces of software you want to track, and you would make the software list its own table. If you just want to have a set list of software and nothing else where each computer has only 1 record then just put it all in the computer table. The database is going to perform faster if it doesn't have to look in more than one table to get a data set, so anything you can group into one table is going to help the performance. The tracking table is up to you, if you only have 1 record where each computer is now then that can also go in the same table as everything else, but if you want to keep a record so that you have a history on where the computer was on any day then there is more than one record per computer there so it needs to be its own table. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.