Need Feedback on table setup

[Little Penny]

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

 

[TC]

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