I have been given the task to design a database for the ICT support team.
This should include an inventory of all computers\ hardware in each room;
when it was purchased; warranty; repair history and so on.
They also want me to keep track of printer stock levels as well.
I am eager to start this but simply have no idea what field should go in
what table for best results.
I was thinking that I should have one table as TblLOCATION and populate it
with all the rooms in our school.
Then I created another table called TblMain with the following headings:
I then created a one to many relationship between location and location.
I am not sure what the purpose of a primary key is so I set location in
TblLocation and Model in TblMain to PK.
Then I am wondering if it would be best to have separate tables for the
Sorry for such a long post. I would really appreciate any thoughts on how to
set my DB out.
A lot of this depends on how much "normalization" you want in your database. A normalized database doesn't have (much) reduandtant data in it, and the data has more "integrity". But a database that is not normalized is easier to develope (imagine keeping track of this on a single Excel Spreadsheet--that would be non-normalized). With that in mind, I would think you need at *least* 3 tables:
There would be a 1 to many relationship between tblMain and tblLocation (as you stated) and a 1 to many relationship between tblModel and tblMain. So, in the model table you would have: make, description, manufacturer etc. The tblMain would have all your assets in it (ModelPK, LocationPK, warranty, date purchased, notes, history). By seperating the the tblMain and tblModel your data integrity with be much better than if you just tracked the model in the tblMain table (i.e. someone might enter in 'HP LaserJet 5000' as a model and someone else might enter 'Hewlitt Packard LaserJet 5k' having a list to pick from reduces the chances of this happening)
As far as Primary Keys go, they are what uniquely identify that record. There is probably much debate over what makes a primary key good, some folks like the AutoNumber field and some people prefer something "meaningful". The Model field in tblMain is probably not a good choice. You can have several assets that are the same model in it. For hardware, if you use AssetTags in your school, they might be a good Primary Key, but personally I wouldn't use it (i've got my reasons). In your case I would probably just create a field of type 'AutoNumber' and use that as the primary key for the tblMain table.
As far as your colink blkink tables go, I'm not quite sure. Is that the "stock" table? (e.g. how much ink you have in stock). If so, you can create a stock table (tblStock) that holds that information. Probaly one table for both color and black. There's numerous ways to do this, but for simpicity, I would just create the table and have it track how many items are 'On Hand', 'On Order' and such.
Another table you might want:
tblAssociates - The people that are assigned to equipment