If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need Feedback on table setup
Ok after reading some of the feedback, I went back and redesigned my tables. In addition, here is an overview of what I want this database to do.
1. Tel me all equipment that in a particular location 2. Computers – To know the location of a computer, what monitor is connected, Peripherals if any, User name if not a multi user computer, Printer(s) attached if slaved 3. Monitors attached to what computer and location 4. Peripherals – Location, User assigned if applicable, Location, and Computer attached if applicable. 5. Printers – Location, Computer attached if applicable 6. Laptops – User 8. User – Everything associated with User I have taken out the software table as that will be a little complex right now. Looking for feedback george Redesigned Tables (PK)=Primary Key Table: tbl_Computers Name Type Size Computer_Manufacturer Text 50 Computer_Model Text 50 Computer_SerialNumber (PK) Text 50 Computer_ProductID Text 50 Computer_OperatingSystem Text 50 Computer_RAM Long Integer 4 Computer_CPU_Type Text 50 Computer_CPU_Speed Text 50 Computer_HDD_Manufacturer Text 50 Computer_HDD_Size Long Integer 4 Computer_HDD_SerialNumber Text 50 Computer_HDD_IDE Yes/No 1 Computer_HDD_SCSI Yes/No 1 Computer_HDD_Serial_ATA Yes/No 1 Computer_Server Yes/No 1 Computer_DVDRW Yes/No 1 Computer_DVD Yes/No 1 Computer_CDRW Yes/No 1 Computer_CDRom Yes/No 1 Computer_Floppy Yes/No 1 Computer_Wired_MAC Text 50 Computer_Wireless_MAC Text 50 Computer_Purchase_Date Date/Time 8 Computer_Domain_Name Text 50 Computer_Firewire Yes/No 1 Computer_USB Yes/No 1 Computer_Notes Memo - Computer_Barcode Text 50 Computer_Warranty_ Expires Date/Time 8 UserID Long Integer 4 Monitor_SerialNumber Text 50 SoftwareID Long Integer 4 Peripheral_SerialNumber Text 50 LocationID Long Integer 4 Table: tbl_Laptops Name Type Size Laptop_Manufacturer Text 50 Laptop_Model Text 50 Laptop_SerialNumber (PK) Text 50 Laptop_ProductID Text 50 Laptop_OperatingSystem Text 50 Laptop_RAM Long Integer 4 Laptop_CPU_Type Text 50 Laptop_CPU_Speed Text 50 Laptop_HDD_Manufacturer Text 50 Laptop_HDD_Size Long Integer 4 Laptop_HDD_SerialNumber Text 50 Laptop_HDD_IDE Yes/No 1 Laptop_HDD_SCSI Yes/No 1 Laptop_HDD_Serial_ATA Yes/No 1 Laptop_DVD Yes/No 1 Laptop_DVDRW Yes/No 1 Laptop_CDRom Yes/No 1 Laptop_CDRW Yes/No 1 Laptop_Floppy Yes/No 1 Laptop_Wired_MAC Text 50 Laptop_Wireless_MAC Text 50 Laptop_Purchase_Date Date/Time 8 Laptop_NetworkName Text 50 Laptop_Firewire Yes/No 1 Laptop_USB Yes/No 1 Laptop_Notes Memo - Laptop_Barcode Text 50 Laptop_WarrantyEnd_Date Date/Time 8 Laptop_Signature Yes/No 1 Laptop_Date_U_Received Date/Time 8 Laptop_Date_U_Returned Date/Time 8 UserID Long Integer 4 Table: tbl_location Name Type Size Location_Building Text 50 Location_RoomNumber Text 50 Location_Department Text 50 UserID Long Integer 4 LocationID (PK) Long Integer 4 Table: tbl_Monitors Name Type Size Monitor_Manufacturer Text 50 Monitor_Model Text 50 Monitor_ProductID Text 50 Monitor_Size Text 50 Monitor_SerialNumber (PK) Text 50 Monitor_Purchase_Date Date/Time 8 Monitor_WarrantyExpires Date/Time 8 Flat_Screen Yes/No 1 CRT Yes/No 1 Monitor_Notes Memo - Computer_SerialNumber Text 50 LocationID Long Integer 4 Table: tbl_Peripherals Name Type Size Peripheral_Type Text 50 Peripheral_Manufacturer Text 50 Peripheral_Model Text 50 Peripheral_SerialNumber (PK) Text 50 Peripheral_ProductID Text 50 Peripheral_Purchase_Date Date/Time 8 Peripheral_Notes Memo - Peripheral_MAC_Address Text 50 Peripheral_DNS Text 50 Peripheral_IP Text 50 Peripheral_Barcode Long Integer 4 Peripheral_Signature Yes/No 1 Peripheral_Date_U_Received Date/Time 8 Peripheral_Date_U_Returned Date/Time 8 Peripheral_Excessed Yes/No 1 Peripheral_ExcessedDate Date/Time 8 Peripheral_WarrantyExpires Date/Time 8 UserID Long Integer 4 Computer_SerialNumber Text 50 LocationID Long Integer 4 Table: tbl_Printers Name Type Size Printer_Manufacturer Text 50 Printer_Model Text 50 Printer_SerialNumber (PK) Text 50 Printer_ProductID Text 50 Printer_DNS_Name Text 50 Printer_Network_Printer Yes/No 1 Printer_Slave Yes/No 1 Printer_fpc1_Name Text 50 Printer_IPaddress Text 50 Printer_Wired_MAC Text 50 Printer_Purchase_Date Date/Time 8 Printer_Excessed Yes/No 1 Printer_ExcessedDate Date/Time 8 Printer_WarrantExpires Date/Time 8 Printer_Notes Memo - LocationID Long Integer 4 Table: tbl_UserInfo Name Type Size UserID (PK) Long Integer 4 User_FName Text 50 User_LName Text 50 User_PhoneExt Text 50 User_CellPhone Text 50 User_Pager Text 50 User_Email Text 50 LocationID Long Integer 4 ComputerID Long Integer 4 |
#2
|
|||
|
|||
Need Feedback on table setup
Comments inline
(PK)=Primary Key Table: tbl_Computers ================= Computer_Manufacturer Text 50 Computer_Model Text 50 Computer_SerialNumber (PK) Text 50 What if two manufactures supply two computers with the same serial number? In that case, you could not enter both of them, with you current primary key. You could solve this problem (if it's a problem) by using an autonumber as the primary key. Pronally, I'd be tempted to do that. Computer_ProductID Text 50 Computer_OperatingSystem Text 50 Computer_RAM Long Integer 4 Computer_CPU_Type Text 50 Computer_CPU_Speed Text 50 Computer_HDD_Manufacturer Text 50 Computer_HDD_Size Long Integer 4 Computer_HDD_SerialNumber Text 50 Computer_HDD_IDE Yes/No 1 Computer_HDD_SCSI Yes/No 1 Computer_HDD_Serial_ATA Yes/No 1 Computer_Server Yes/No 1 Computer_DVDRW Yes/No 1 Computer_DVD Yes/No 1 Computer_CDRW Yes/No 1 Computer_CDRom Yes/No 1 Computer_Floppy Yes/No 1 Computer_Wired_MAC Text 50 Computer_Wireless_MAC Text 50 Computer_Purchase_Date Date/Time 8 Computer_Domain_Name Text 50 Computer_Firewire Yes/No 1 Computer_USB Yes/No 1 Computer_Notes Memo - Computer_Barcode Text 50 Computer_Warranty_ Expires Date/Time 8 Those fields look ok; they are all attributes of the computer & nothing but the computer. UserID Long Integer 4 Might you want a history of which users had what computers when? If so, you would not want UserID in there. You'd want a seperate table like the follwing: ComputerID } composite UserD } primary DateFrom } key Monitor_SerialNumber Text 50 ... Peripheral_SerialNumber Text 50 That's a bit denormalized. In theory, you need a seperate table to show the zero, one or more peripherals for the current computer. But it would be ok if you do no need to store any other attributes of each peripheral, apart from its SerialNumber. Table: tbl_Laptops =============== I don't see why you have a seperate table for this. A laptop is just another kind of computer. On a quick look, most of the fields seem identical between the two tables. I say you should have /one/ table, with a flag to indicate the type of computer: mainframe, desktop, laptop, whatever. Table: tbl_location ============== Location_Building Text 50 Location_RoomNumber Text 50 Location_Department Text 50 UserID Long Integer 4 LocationID (PK) Long Integer 4 What is the UserID doing in there? That is saying: "Each location has at most one user". What if one location has five users? Those are some thoughts to begin with! HTH, TC 4 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
Seeking some expert advice. | HD87glide | Using Forms | 14 | March 23rd, 2005 10:11 PM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |
Manual line break spaces on TOC or Table of tables | Eric | Page Layout | 9 | October 29th, 2004 04:42 PM |
Complicated Databse w/many relationships | Søren | Database Design | 7 | July 13th, 2004 05:41 AM |