brighteyes
Registered User.
- Local time
- Today, 13:13
- Joined
- Oct 12, 2004
- Messages
- 14
Here’s a little scenario:
An engineer requests that “x” amount of computers are ordered for the company. The estimated costs of the system(s) are determined. The estimated cost of the systems is forwarded to a manager, and the request must be approved by a manager before the order is placed. Once the manager approves the request, the order is placed through the ordering company. After the ordering company approves the order, an order number is assigned and serial number(s) specific to each computer is associated with that order number. At this point it is possible to track the order status via existing programs using the assigned order number and serial numbers. After it is determined through the existing software that the order has been shipped, invoiced, and received by the Receiving department, I need to physically go pick up the systems and submit them into my Central Inventory. From the central inventory, systems will be checked out to employees as needed, checked back in when testing is finished, or transferred internally between employees.
Currently I have a database to manage the “Check In/Check Out” Process. This involes 2 tables:
Employee
empID – Primary key
empType- Regular, Manager, Tech
empFirstName
empLastName
empPhone
System
ServiceTag – Similar to serial number, primary key
LineOfBusiness – (Make of System. Ex: optiplex, precision, latitude, inspiron)
MarketingCode – (Model of System. Ex: 8600, gx280, gx260)
Description – (HDD size, Video Card, Memory, Configurations…)
Status – (Checked in, Checked out temporarily, checked out permanently, Unable to Locate)
empID – (Foreign key to Employee table)
The tables are linked together by empID. I am having no problems with the current design. My question lies in the former part of the process; tracking from the point of initial request to physically picking up the systems from receiving and checking them into the inventory. Currently my database is based on the ServiceTag and the physical system, and tracking a request lacks that critical piece of information until the order number is assigned.
Here is the data flow from the process outlined above:
1.Engineer comes to me with a request for systems. He will provide me with the LineOfBusiness, MarketingCode, Description, and Quantity requested.
2.The estimated cost of the systems is determined.
3.The estimated cost along with the initial request from the engineer is forwarded to the manager.
4.The manager replies with “approved,” or “denied.”
5.The order is placed through the ordering company. (Order data is the same as the engineers initial request)
6.The ordering company either approves or denies the order.
7.If the order is approved, an order number is assigned along with the corresponding service tags (One order number can have many Service Tags)
8.I will be notified when the Receiving Department receives the systems.
9.I will physically pick up the systems and check them into my inventory
These 9 steps is what has me stumped. Ive brainstormed request tables and order tables and status tables and I haven’t made very much progress or sense of it all. Would anyone be so generous as to give me some ideas on table structure and establishing relationships with the current design? There may be some ambiguity that needs to be cleared up, any questions are welcomed and encouraged, and I’m happy to try to clarify anything. Thank you so much for your help.
An engineer requests that “x” amount of computers are ordered for the company. The estimated costs of the system(s) are determined. The estimated cost of the systems is forwarded to a manager, and the request must be approved by a manager before the order is placed. Once the manager approves the request, the order is placed through the ordering company. After the ordering company approves the order, an order number is assigned and serial number(s) specific to each computer is associated with that order number. At this point it is possible to track the order status via existing programs using the assigned order number and serial numbers. After it is determined through the existing software that the order has been shipped, invoiced, and received by the Receiving department, I need to physically go pick up the systems and submit them into my Central Inventory. From the central inventory, systems will be checked out to employees as needed, checked back in when testing is finished, or transferred internally between employees.
Currently I have a database to manage the “Check In/Check Out” Process. This involes 2 tables:
Employee
empID – Primary key
empType- Regular, Manager, Tech
empFirstName
empLastName
empPhone
System
ServiceTag – Similar to serial number, primary key
LineOfBusiness – (Make of System. Ex: optiplex, precision, latitude, inspiron)
MarketingCode – (Model of System. Ex: 8600, gx280, gx260)
Description – (HDD size, Video Card, Memory, Configurations…)
Status – (Checked in, Checked out temporarily, checked out permanently, Unable to Locate)
empID – (Foreign key to Employee table)
The tables are linked together by empID. I am having no problems with the current design. My question lies in the former part of the process; tracking from the point of initial request to physically picking up the systems from receiving and checking them into the inventory. Currently my database is based on the ServiceTag and the physical system, and tracking a request lacks that critical piece of information until the order number is assigned.
Here is the data flow from the process outlined above:
1.Engineer comes to me with a request for systems. He will provide me with the LineOfBusiness, MarketingCode, Description, and Quantity requested.
2.The estimated cost of the systems is determined.
3.The estimated cost along with the initial request from the engineer is forwarded to the manager.
4.The manager replies with “approved,” or “denied.”
5.The order is placed through the ordering company. (Order data is the same as the engineers initial request)
6.The ordering company either approves or denies the order.
7.If the order is approved, an order number is assigned along with the corresponding service tags (One order number can have many Service Tags)
8.I will be notified when the Receiving Department receives the systems.
9.I will physically pick up the systems and check them into my inventory
These 9 steps is what has me stumped. Ive brainstormed request tables and order tables and status tables and I haven’t made very much progress or sense of it all. Would anyone be so generous as to give me some ideas on table structure and establishing relationships with the current design? There may be some ambiguity that needs to be cleared up, any questions are welcomed and encouraged, and I’m happy to try to clarify anything. Thank you so much for your help.
