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
|
|||
|
|||
Table Design Help.
Am designing an inventory database and I have difficulty designing the
tables and relationships. We have about 24 different types of equipments, 6000 customers and 4 warehouses. In total, we approximate about 6000 * 15 + ([Equipment at warehouses] + [equipment at vendor repair]). This of course a high estimate because a customer can never have all the 24 equipment types installed at their location. For 6 of the equipment types, each equipment has a unique serial number. For an equipment to be installed, there must be a work order for the install. The same applies for a removal. If the equipment is not installed at a customer location, it is either in one of the warehouses or at the vendor repair. At the end of the month, we bill for the Installation of certain equipments, removal of certain equipments. We also bill certain equipments a monthly service fee calculated on a daily rate basis. Others can only be billed the service fee if they have been installed for a full month. We also need to know what equipment is where as of now. We need a summary count and an itemized listing. (In a week, we have about 10 to 20 customers requesting an install or a removal; our techs can therefore easily provide feedback on equipment movement.) Equipments can also be swapped out incase of a malfunction. We are mainly interested in tracking movement for equipments with a serial number (in total less than 1000 equipments have a serial number). Certain equipments are also serviced after certain number of days. Weekly we need to report on the maintenance status of each equipment installed at a location. All equipment a location is serviced when service is done. We plan to use MS Access 2003 for this. Thank you, Abu. |
#2
|
|||
|
|||
Table Design Help.
Big question!
The first thing you have done right is to begin defining exactly what the database needs to do. This post is the initial step of writing a document that defines the project. It will end up as a Word document many pages long -- possibly 10,000 words by the time you have all aspects nutted out. As you create that document, you will be trying different data structures, pinning down all the one-to-many relations to cover what you want. At very least, and based on what you have defined so far, you will need these tables: EquipType: one record for each type of equipment. Equip: one record for each piece of equipment (unique serial number) Company: one record for each customer (including own company) Location: (including 4 warehouses for own company) Contract: one record for each customer agreement ContractDetail: line items in a contract Repair: one record for each repair docket RepairDetail: line items in a repair docket Employee: one record for each staff member WorkOrder: one record for each request WorkOrderDetail: line items in a request Service: one record for each service conducted ServiceDetail: line items of what was done to what equip in a service ServiceType: lookup table for types of serivce EquiptypeServiceType: defines the regular maintentence needed for each type of equipment (including frequency.) & other tables to handle receiving of new equipment. As you examine that, other alternatives will come to mind to consider. For example, it might be really neat if you only had one table to look in to find out the status and count of all pieces of equipment at any one time. So, you might modify the structure so you assign equipment to a company instead of handling installs, removals, sending out to repairs, and receipt of new equipment. New equipment would be an assignment to your own company. Installations would be an assignment to another company. Removals would be assignment back to your company again. Repairs would be hanldled as assignment to a repair company, and when the fixed gear is returned, it is an assignment back to your own company again. This means you handle all these with just these 2 tables: Assign: AssignID primary key CustomerID Number AssignDate date/time AssignDetail: AssignDetailID primary key AssignID foreign key EquipID foreign key That's just one example of the kind of thought processes you will engage in as you write the specification for the job, and - in parallel - consider various data structures to identify the simplest solution that meets all your needs. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Abu" wrote in message oups.com... Am designing an inventory database and I have difficulty designing the tables and relationships. We have about 24 different types of equipments, 6000 customers and 4 warehouses. In total, we approximate about 6000 * 15 + ([Equipment at warehouses] + [equipment at vendor repair]). This of course a high estimate because a customer can never have all the 24 equipment types installed at their location. For 6 of the equipment types, each equipment has a unique serial number. For an equipment to be installed, there must be a work order for the install. The same applies for a removal. If the equipment is not installed at a customer location, it is either in one of the warehouses or at the vendor repair. At the end of the month, we bill for the Installation of certain equipments, removal of certain equipments. We also bill certain equipments a monthly service fee calculated on a daily rate basis. Others can only be billed the service fee if they have been installed for a full month. We also need to know what equipment is where as of now. We need a summary count and an itemized listing. (In a week, we have about 10 to 20 customers requesting an install or a removal; our techs can therefore easily provide feedback on equipment movement.) Equipments can also be swapped out incase of a malfunction. We are mainly interested in tracking movement for equipments with a serial number (in total less than 1000 equipments have a serial number). Certain equipments are also serviced after certain number of days. Weekly we need to report on the maintenance status of each equipment installed at a location. All equipment a location is serviced when service is done. We plan to use MS Access 2003 for this. Thank you, Abu. |
#3
|
|||
|
|||
Table Design Help.
The discussion has brought a new perspective to the design; the
'assignment' model. I will review this. Thank you, Abu. |
Thread Tools | |
Display Modes | |
|
|