Table Design Help.

[Abu]

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.

 

[Allen Browne]

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Abu]

The discussion has brought a new perspective to the design; the
'assignment' model.
I will review this.

Thank you,
Abu.