Database Design
I have a question that is not really directly associated with the book but maybe someone can help me. Im trying to decide on a design for my SQL server database. this database will house information about inventory for an IT dept., such as computers, printers, monitors..etc. I was thinking that what I should do is have a table that contains fields such as inventoryid(autoincrement integer),a location field, manufacturer type, and item type(such as printer or monitor)....then have subsequent tables that contain the details on these particular types of inventory like the details that would pertain only to monitor items or computers...considering that the information on these items is quite different. my problem is this...how do i set this up? should i have the inventoryid be the primary key in all tables and then just set up a 1 to 1 relationship between the main items table and each particular item type table (like a 1 to 1 relationship between the main inventory table and the printers table with the inventoryid field being the foreign key in the printers table)? that sounds ok to me but the problem with that is making sure the inventoryid stays unique...the only way i can think to do that is by adding an item to the main table first, then after it is assigned an autoincremented id, somehow select that id and use it to add the details record to the corresponding details table (the monitors table would have fields like size,a boolean to store whether it is a flatscreen or not,etc....the computers tabe will have fields like processor speed, hd size, domain name, etc)......OR should i just have one table that contains fields for every type of item and then just apply nulls to fields that dont apply for particular items?
i hope this made some sort of sense
any suggestions are greatly appreciated
thanks in advance
Justin
|
