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
|
|||
|
|||
2nd post Database design different attributes
Hi all, Kindly help me out on this one. I am having a computer shop and we have many IT related equipments nearly 30 categories which comprises of Servers, Laptops, PC's, Printers, routers, switches, monitors, Software etc... Now each of the aboved mentioned category have different attributes such as Servers may have different configuration like raid, SCSI Hdd, etc, Laptops may have different attributes from that of PC's, similarly printers, scanner and monitors have different attributes which are in no way related. Output needed : a) To maintain and track all the IT equipment which includes warranty etc for all the equipments. b) To check on support/service of equipments when I hand them over for repair for example. c) To generate invoices for customers who take or rent stuff from us. Design I tried to design this by keeping the following tables A) 1) tblManufacturers -- Includes all the manufacturer details eg: HP, ACER, etc 2) tblSuppliers -- Includes all our supplier information. 3) tblAssetCategories -- Includes all the nearly 30 above mentioned eg: Laptop, Servers, Printers, Monitors, etc. 4) tblCommonAttributes -- Make, Serial No, Warranty Info, Purchasedate, Purchasecost, etc 5) tblComputer -- Casing, Floppy, USB, MotherBoard, Processor, RAM, etc 6) tblServer -- RaidController, SCSI Type, TapeDrive etc 7) tblLaptop -- battery type, LCD screen type, Touch pad, 8) tblLaserPrinter -- PPM, RAM, trays, etc 9) tblDeskjet -- Catridges details etc. similarly for the each asset tblRouter, TblSwitch with their attributes. B) tblCustomer --contains customer details tbltransactions --contains details of all the transactions carried out Related in 1 -- Many -- 1 with AssetID (obtained from the various tables) C) tblServiceRelated --- Conatins Items which are under repair/service. tblScrap -- Items that need to de disposed I also thought of trying to use One to One relation but that just adds up to the confusion. Problem area: 1) How can I link the Primary Key*s* from all the various tables such as tblComputer which has PK AssetID for every computer and also from tblLaptops (PK AssetID) , tblMonitors(PK AssetID) , tblPrinters (PK AssetID) , etc into the tblTransactions? Also, I need to have select AssetID when I send them out for service or to Scrap. 2) If I rent out certain equipment to any customer and if that equipment is faulty we take it down to repair and assign him another one till the equipment is fixed and returned and we do not charge him for that much period for that month. Any Ideas please. Please do let me know if my approach is correct and if any changes need to be done. Awaiting eagerly for your replies thanks in advance. -- Regards, B |
#2
|
|||
|
|||
Burghew
Something to consider ... (and you didn't mention how tables of equipment type are related) Since each of the different types of equipment have both common and unique attributes, you could use one table to "register" all of them, and your tblLaptop, tblDesktop, tblLaserPrinter tables to further define them. I believe this would also allow you to register the same type of equipment from different manufacturers. The equipment-attribute tables would use the Primary ID from the main (register) table as their (non-autonumber) Primary ID. This would represent a 1:1 relationship. With this design, you don't have to look to multiple tables to find the ID for your Transactions -- you just use your tblEquipment (register/common attributes) table's ID, and a "type" field. You also use this to know which of the specific tables to look to for specific attributes. As for an approach to record the "bookings" (equipment-to-customer), do you have a yes/no field related to "Billable"? -- Good luck Jeff Boyce Access MVP "Burghew" wrote in message ... Hi all, Kindly help me out on this one. I am having a computer shop and we have many IT related equipments nearly 30 categories which comprises of Servers, Laptops, PC's, Printers, routers, switches, monitors, Software etc... Now each of the aboved mentioned category have different attributes such as Servers may have different configuration like raid, SCSI Hdd, etc, Laptops may have different attributes from that of PC's, similarly printers, scanner and monitors have different attributes which are in no way related. Output needed : a) To maintain and track all the IT equipment which includes warranty etc for all the equipments. b) To check on support/service of equipments when I hand them over for repair for example. c) To generate invoices for customers who take or rent stuff from us. Design I tried to design this by keeping the following tables A) 1) tblManufacturers -- Includes all the manufacturer details eg: HP, ACER, etc 2) tblSuppliers -- Includes all our supplier information. 3) tblAssetCategories -- Includes all the nearly 30 above mentioned eg: Laptop, Servers, Printers, Monitors, etc. 4) tblCommonAttributes -- Make, Serial No, Warranty Info, Purchasedate, Purchasecost, etc 5) tblComputer -- Casing, Floppy, USB, MotherBoard, Processor, RAM, etc 6) tblServer -- RaidController, SCSI Type, TapeDrive etc 7) tblLaptop -- battery type, LCD screen type, Touch pad, 8) tblLaserPrinter -- PPM, RAM, trays, etc 9) tblDeskjet -- Catridges details etc. similarly for the each asset tblRouter, TblSwitch with their attributes. B) tblCustomer --contains customer details tbltransactions --contains details of all the transactions carried out Related in 1 -- Many -- 1 with AssetID (obtained from the various tables) C) tblServiceRelated --- Conatins Items which are under repair/service. tblScrap -- Items that need to de disposed I also thought of trying to use One to One relation but that just adds up to the confusion. Problem area: 1) How can I link the Primary Key*s* from all the various tables such as tblComputer which has PK AssetID for every computer and also from tblLaptops (PK AssetID) , tblMonitors(PK AssetID) , tblPrinters (PK AssetID) , etc into the tblTransactions? Also, I need to have select AssetID when I send them out for service or to Scrap. 2) If I rent out certain equipment to any customer and if that equipment is faulty we take it down to repair and assign him another one till the equipment is fixed and returned and we do not charge him for that much period for that month. Any Ideas please. Please do let me know if my approach is correct and if any changes need to be done. Awaiting eagerly for your replies thanks in advance. -- Regards, B |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Free Access Training | Timboo | New Users | 8 | August 17th, 2005 05:58 PM |
I need help with my design Database Requirements.xls (01/01) | Database Design | 2 | December 7th, 2004 02:32 PM | |
cannot change password | Richard | General Discussion | 13 | November 14th, 2004 11:00 PM |
Resumé Database Design | Denis | Database Design | 2 | September 2nd, 2004 06:58 PM |
You do not have exclusive access... ERROR | Robin | General Discussion | 1 | July 6th, 2004 01:18 AM |