Stock Management System Help

[samvb]

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.

[Frinavale]

I did not download anything that you have attached but it sounds like you need to sit down and normalize your system.

If you need to track an item, then you need to create a table that will hold a record for that item being tracked. The record for the tracked-item will have to have some sort of unique identifier.

Then you need to create a table for the tracked-item's history. That table will have a foreign key to the table that contains the tracked-item.

This way you can have a tracked-item (in the tracked-item table) and multiple pieces of history for that item (in the tracked-item-history table which is linked to the tracked-item table through the foreign key).

Check out this awesome article on Database Normalization & Table Structures

-Frinny

[ariful alam]

Nice topic.

I gonna try this with a long time.

Thanks

[Mihail]

Hi !
Note please that every one of us can give you an idea but no one of us can solve entire problem (in a few posts, of course).

So, this is my idea:

Create two more (virtual) stores:
1) WorldStore - where you create first time the items, with a structure like this: ID_WS, ItemName, Quantity
2) KillStore - where all "dead" items will be, finally stored ID_KS, ID_WS (Foreign Key), Quantity

This way you remove the differences between consumable and non-consumable items.
Even in the real life is no difference between this items because, in time, all this items will be "killed": the fuel after few days, a computer after few years and a building after a lot of years.

From now, you can remove also the main difference between an employe and a store.
Each one is, in fact, a store, because each one temporary store a part from the item "Quantity".
So your "simplified" problem is to move items between this stores (using ID_WS as foreign key to refer a certain item and with some restrictions, of course: From the WorldStore are allowed only the transfers to the "true" stores, an employe can transfer the items directly to the KillStore or to the store from where that item come to him, maybe other restrictions that I can't see now).

Finally each item will be moved in KillStore.
The "store" that represent an employe with a car will move the fuel in this KillStore and a computer from a "true" store will be also moved in this KillStore when it is "out of work".
From time to time, if necessary, you can remove all items from this "store" in order to decrease the database size.

Hope this is a help for you.
Unfortunately also my English is not very good.

Good luck !

[zmbd]

Comeing to us to help design your school project is a lot like cheating.

You seem to have a good start on solving the issue and I second the required reading on normalization as given in the link by Frinavale in post #2.

If you get stuck on a specific part of your design, then certainly post back with your code, sql, form, etc... with a specific question...

You also need to read the followinging: How to Ask Good Questions and you need to read the link therein to Posting Guidlines Esp. part about homework.

Basic steps to good DB design are easily found by a Google or Bing... or even in your textbook and is where you need to refer... you have a good start...

-z