building correctly a DB

pelegk2

i am building a DB that controls all the pc and there parts in my company
my question is :
if for example i have 2 types of mouse : serial and usb
and 2 types of keyboards- usb and wirless
will i open for each of the mouse/keyboard a seperate table?
or what the correct way to do this?
thnaks i nadvance
peleg

Roger_Ramjet

Judging from your post you are way off track with your understanding of relational database design. The correct way is to normalise your data and derive your db design from that. I don't know of a good link to teach you how to normalise unfortunately, a search of this forum may turn one up.

Without more information I can only give you a rough guide to what you need, but it is definately not multiple tables. for things like keyboards.

You need one master table for the PCs. It would have fields for e.g. id/serialno, inventory tag, date bought, value/price, make, model, ip address, workstation name, location , domain/workgroup, user/department, operating system (if this varies at all), hard disk type, mouse type, keyboard type, monitor type, network card, etc.
You would have one or more tables for spare parts, the fewer the better so if one will do then only have one. How many you need depends upon what information you need to store. At this point you need to think about how you are going to use the data, this should define what fields you need. Remember, it is usually better to have one table with some fields that are empty/not applicable for some rows, than to have lots of tables where all the fields are full.
If you have a great variety of PC configurations then you will need a table that links which parts are applicable to which systems. Otherwise, you can just use the part id as the 'type' identifier in the master inventory.
You would also have one or two tables in which you store faults/service history.

pelegk2

second i didnt undertsand what the correct way to hold the dat like types of mouse or keyboard?

Roger_Ramjet

If you are creating an inventory of parts with several fields for each item, then you create a unique id for each item and use this in the main table. You then use this id field to join the main table with the part details as and when you need to include them in your application.

see
http://dev.mysql.com/doc/mysql/en/SELECT.html
http://dev.mysql.com/doc/mysql/en/JOIN.html

parts table
part_id, class, type, make, model, spec
1, mouse, usb, dell, intellimouse,
2, mouse, serial, microsoft, lousymouse,
3, hard disk, ide, seagate, sg854025, 40GB

parts inventory
item_id, part_id, date bought, date used
1, 3, 20/06/2004, 12/07/2004
1, 1, 21/06/2004,

system table
system_id, make, model, serial no, mouse, etc