Sorry to ask this, im a complete novice at DB but im trying to make a database for my place of work which holds an inventory of all the machines and peripherals in the building. I have so far made a spreadsheet of everything but am struggling on how exactly to normalize everything. I will list the fields I have in my spreadsheet below. I just want to know how to normalize and if I will need any more fields in order to do this successfully.
Equipment Number - Unique number assigned to all PCs and peripherals e.g CED00201
Staff/Trainee - whether the item is used by staff or trainees (students)
Item Type - what the item is e.g PC or Printer
Seller - where the item was purchased
Date of Purchase - when the item was purchased
Item Description - a description of the item e.g Mouse/Keyboard brand and type
Tag No/Serial Number - the PCs tag number or the items Serial number (always unique to item)
Computer Name - Name of the computer in the network (unique)
Warranty - when the warranty expires
Location - the room the item is in
Site - a number between 1 and 30 of possible locations in the building (each number assigned on a map of building)
In terms of normalisation, your main problem is Site. This should be inferred from location.
There are other things you might want to consider:
Are all items really only used by staff OR students?
You might want to consider a batch table (something like an invoice header) containing all the stuff related to a batch (e.g. order date, vendor etc) instead of recording for each item.
I would allow multiple notes per item, timestamped and signed for.
Do staff own items, and if so does only one ever own them?
The place is set up so that PCs are used by either students or staff as the staff have thier own 'office' and the student PCs are in a more classroom environment! Obviously things like printers are shared though.
I was really just hoping to get an idea, i did normalization at school but its one of the only things i just couldnt get my head around, i know the general idea behind what has to be done and the "the key, the whole key and nothing but the key" thing but for some reason i couldnt understand how to go about the actual normalization process!!
Im really just looking for the best way to get the data listed into some sort of database so that editing, adding and searching is easily done as the spreadsheet has so much in it currently that it can be very messy looking and not very intuitive which is what i am looking as I am only on placement and someone will be coming in after me and be using the database to maintain the inventory.