Jump to content

Same Primary Key


Recommended Posts

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

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 post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...