crazycat503
New member
- Local time
- Today, 04:36
- Joined
- May 23, 2011
- Messages
- 3
Hey All!
Need your help with this complex database (at least for me). I have gave it a shot, changed it 4 times but still couldn't come up with a solution. First, please don't suggest me to purchase or use other software or so out there as this is a school project and i wanna give it the best shot before I began copying ready-made designs.
It is a stock management system for a company. The company purchases several items from its providers/suppliers. There are specific divisions with in the company that are allowed to accept those items and place them in Stores. Then the items could be requested by its employees working in different divisions.
The company structure is as follows : Head is at the top. A head could have multiple DIVISIONS. Each division is made up of departments. Now, employees work in departments.
Stores
A store could be owned by head, division or both. That means when an employee needs to take an item, he must get it from a store his head or division is allowed to access/search in.
Transactions
The incoming invoice is used when items first arrived from providers. It contains info such as no,date,provider,store it is saved to (TBLINCOMINGINVOICES). While for internal transactions, another invoice is used with 8 digits invoice no,date,source store,responsible employee. In the end, I need the complete history of an item, like the invoice it came with to down the employee using it. If an employee,head,divsion wants to return items it took such as computers, it can only return it to the store it took it from directly. That is why I have sourcestore in the tbloutgoingitemslist.
Problem
What is needed now is to keep history of an item fully in the company since its arrival date. That is when providers provide items, they do so with an invoice containing list of items they are providing. Only divisions with master stock rights could accept items from providers. Now, once the items arrive, they are distributed thru out the company. Other heads/division could have their own stores as well. Now, my problem is tracking an items exact location thru out the company which is made up of several head sections, each head with its own division and each division with its own departments where employees work.
An item could be present in a store that is owned by a head/division or it may be with a specific employee. In such distributed area, how could i keep the transaction? Because I want to know who gave who? A head/division could take many equipments at once and save it in one of its stores then distribute it to its employees step by step. In such cases, an employee of the head is responsible for taking the items and that person is mostly storekeeper of the head.
More problem is with consumable items such as fuel. Say initially with one invoice, 1000 liters of fuel arrived. That fuel may have been given to heads/divisions or even employees. How would I safely keep track of it and learn when it is finished? If 100l is given to a divsion for use by its employees, when its employees need to take fuel, i need to search within that 100l alone.
A computers (non-consumable) full transaction is like this:
ARRIVED ON 9/5/2012 with INVOICE NO 251511 and was stored in STORE 10B. ON 1/6/2012, it was given to employee DANIEL with invoice 12121212. Daniel returned it to store 10B on 7/7/2012.
For non-consumable,
1000 Liters arrived on 2/2/2012 with INVOICE NO 241515 and was stored in store 10C. On 3/3/2012, HEAD 1 took 500liters to distribute it to its EMPLOYEES with invoice 88457545.
Here, employees of HEAD 1 will take fuel from the 500 given to thier head. The distribution will look like this..how would I track all this safely and know when an item is in store or out of hand, in which store, with whom...that sort of thing but in full detail.
My english is not good but I hope you pretty much get the idea. Please refer to a squeezed version of the database I have included here.
Need your help with this complex database (at least for me). I have gave it a shot, changed it 4 times but still couldn't come up with a solution. First, please don't suggest me to purchase or use other software or so out there as this is a school project and i wanna give it the best shot before I began copying ready-made designs.
It is a stock management system for a company. The company purchases several items from its providers/suppliers. There are specific divisions with in the company that are allowed to accept those items and place them in Stores. Then the items could be requested by its employees working in different divisions.
The company structure is as follows : Head is at the top. A head could have multiple DIVISIONS. Each division is made up of departments. Now, employees work in departments.
Stores
A store could be owned by head, division or both. That means when an employee needs to take an item, he must get it from a store his head or division is allowed to access/search in.
Transactions
The incoming invoice is used when items first arrived from providers. It contains info such as no,date,provider,store it is saved to (TBLINCOMINGINVOICES). While for internal transactions, another invoice is used with 8 digits invoice no,date,source store,responsible employee. In the end, I need the complete history of an item, like the invoice it came with to down the employee using it. If an employee,head,divsion wants to return items it took such as computers, it can only return it to the store it took it from directly. That is why I have sourcestore in the tbloutgoingitemslist.
Problem
What is needed now is to keep history of an item fully in the company since its arrival date. That is when providers provide items, they do so with an invoice containing list of items they are providing. Only divisions with master stock rights could accept items from providers. Now, once the items arrive, they are distributed thru out the company. Other heads/division could have their own stores as well. Now, my problem is tracking an items exact location thru out the company which is made up of several head sections, each head with its own division and each division with its own departments where employees work.
An item could be present in a store that is owned by a head/division or it may be with a specific employee. In such distributed area, how could i keep the transaction? Because I want to know who gave who? A head/division could take many equipments at once and save it in one of its stores then distribute it to its employees step by step. In such cases, an employee of the head is responsible for taking the items and that person is mostly storekeeper of the head.
More problem is with consumable items such as fuel. Say initially with one invoice, 1000 liters of fuel arrived. That fuel may have been given to heads/divisions or even employees. How would I safely keep track of it and learn when it is finished? If 100l is given to a divsion for use by its employees, when its employees need to take fuel, i need to search within that 100l alone.
A computers (non-consumable) full transaction is like this:
ARRIVED ON 9/5/2012 with INVOICE NO 251511 and was stored in STORE 10B. ON 1/6/2012, it was given to employee DANIEL with invoice 12121212. Daniel returned it to store 10B on 7/7/2012.
For non-consumable,
1000 Liters arrived on 2/2/2012 with INVOICE NO 241515 and was stored in store 10C. On 3/3/2012, HEAD 1 took 500liters to distribute it to its EMPLOYEES with invoice 88457545.
Here, employees of HEAD 1 will take fuel from the 500 given to thier head. The distribution will look like this..how would I track all this safely and know when an item is in store or out of hand, in which store, with whom...that sort of thing but in full detail.
My english is not good but I hope you pretty much get the idea. Please refer to a squeezed version of the database I have included here.
