473,839 Members | 1,485 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Stock Management System Help

228 New Member
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,provide r,store it is saved to (TBLINCOMINGINV OICES). While for internal transactions, another invoice is used with 8 digits invoice no,date,source store,responsib le 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,d ivsion 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 tbloutgoingitem slist.

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.
Attached Images
File Type: jpg rships.jpg (90.1 KB, 17691 views)
Attached Files
File Type: zip db.zip (67.5 KB, 411 views)
Jul 10 '12 #1
4 20035
Frinavale
9,735 Recognized Expert Moderator Expert
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
Jul 10 '12 #2
ariful alam
185 New Member
Nice topic.

I gonna try this with a long time.

Thanks
Jul 10 '12 #3
Mihail
759 Contributor
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 !
Jul 11 '12 #4
zmbd
5,501 Recognized Expert Moderator Expert
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
Jul 15 '12 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

42
3359
by: Matthew Harrison | last post by:
I'm looking for a content management system that works. I've tried phpNuke, phpMyWeb and Jetbox so far. I cannot get any of them to even display the admin screens. I have an Apache 2/RH 9/PHP 4/mySQL setup - all tested via a simple test.php script which does what it is meant to do. However - I get nowhere even getting an error displayed on the above CMS systems.
0
1264
by: lawrence | last post by:
Myself and two friends have been working on a PHP content management system for several months. It's features include weblogs, newsletters, RSS, and a completely customizable admin control panel that can accept multiple templates. The public portion of the site can also accept unlimited templates. We need some help debugging it, so if anyone wants to help, please drop me a line. Also, if anyone wants to help out with coding, we'd love...
17
3205
by: Scott | last post by:
Hi, Can I ask some advice in regards database solutions and content management solutions.? Do you have a philosophy on what is the best for databases - ASP, JSP, Cold fusion, PHP, etc. My company website site is going to be rebuilt and I am the lucky one to do this but I am unsure of the needs and complexity of the project, and what would be the best base code. Do you have any thoughts in this area? The site requires functionality...
5
2504
by: Mudge | last post by:
Hi, I am relatively new to PHP, but I don't care about that. I want to build a database driven Web site that is really a content management system that allows visitors to register and log in and write stories for the web site, and also allows visitors to vote on the articles. I'm thinking that maybe I should use a content management system in existence already because it might save time, then if i need to, i could just change the system...
12
3233
by: jonathan.beckett | last post by:
Hi All, For the past few months I have been working on an open source Apache/PHP/MySQL content management system - and have recently made it available for download. It's still very much a work in progress (current release version is 0.4.6), but you should get a very good idea of what it's about by visiting the site (which uses it, funnily enough), or downloading a copy of it and trying it out.
1
2432
by: Epetruk | last post by:
Hello all, I'm sorry for the long post, but I think it's better if I'm as detailed as I can be so that I don't make a mistake in my choice and so that there's a clear understanding of to what I'm looking for. I'm looking for a content management system to use on a site (http://www.nigeriavillagesquare.com) that I help to moderate. Unfortunately (as I have discovered), there are literally hundreds of CMS's to choose from, so I am...
4
1655
by: Jon | last post by:
All, I'm currently building sites for a fairly small dev shop, and we've run into a subject that we simply don't know where to take. So, here we go: We have built all of our sites statically for the most part, using Dreamweaver Templates, and provided Contribute for the customer to edit the site. So, a while back my boss and our lead dev asked me to look into a Content
6
3156
by: JM | last post by:
I have never used a (content management system) CMS before but I need one for my internship as a webdeveloper. Requirements: runs on Apache, linux or unix, MySQL and PHP (maybe Windows server and IIS) Authentication (not sure yet): existing user database, LDAP or permissions on directories (not sure if that last one is possible) Purpose: scientist working on projects should be able to upload their
1
5796
by: asmusdk | last post by:
I am working on creating a stock management database for a small company. The company receives the merchandise from a handful of suppliers and stocks it before it is sent to the customers – retail outlets. The company works on a concession basis – i.e. the customers do not pay for the delivered goods but sell it on and then take a percentage of the sales. The goods delivered to the retailers can be considered to be “in stock” at the...
0
1023
by: vayalumkan | last post by:
Hi Sir, I want some technical help on how to develop(Implement) a Content Management System using ASP.NET and C#? Can any body help me to develop a CMS (Content Management System) ? Using ASP.NET technology and C#. Database SQL Server 2005
0
9696
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10903
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10645
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10290
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9425
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7827
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4482
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4063
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3131
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.