Solved

inventory system mssql database desgin

Posted on 2013-01-27
5
465 Views
Last Modified: 2013-02-27
i have a table contains company and each company have many branches
and each branch has ROOM like stock , lobby , kitchen..

and i have a table productslist contains products

how i can implement movement of products form  room to room
maybe the movement occurs in the same branch location
or between the two room located  in different branch ???


please can any one help me on implementation


create table company :( companyid , companyname )
create table branch ( branchid , branchname , companyid )
create table room ( roomid , roomname , branchid )
create table products ( productid , productname , serialnumber ,condition )
// condition is : NEW , OLD

first step the products is should be stored in the main company1 bracnh1 room1

than i want to move it to another company name : company2 branch1 room2
or i cam move it to company1 branch2 room1

i want to implement this transaction ? by how ? and i want to save the transaction log...
0
Comment
Question by:afifosh
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:karunamoorthy
ID: 38823913
From your problem definition, it is understood that you have many companies (comp1, comp2, comp3, etc) and each company have many branches ( bran1, bran2, bran3, etc) and each branch have many rooms (room1, room2, room3, etc). ok fine. Now you have products and you want to first place/store the product in a room (companyid+brachid+roomid). i.e. to locate a room you have come from root to find the room ( imagine tree diagram for easy understanding).

Again I am repeating, To locate a room you have tell first companyid then branchid and then rooid. Right!

Imagine you are locating a product (say prodid1 with 50 qty in a room (comp1+bran1+room1)

Now you desing the transaction table which should have productid then companyid then branchid then roomid then qty then may be transaction date etc.

Hence your transaction table should be like this

(Productid, companyid, branchid,roomid,qty,date)

I hope you get some understanding with this.

One samll correction in your table design I like to point out here is

create table room ( roomid , roomname , branchid ,companyid) --- Here I am adding companyid.I hope you followed this.

Please post your comments here for further help!

Have a nice day
0
 
LVL 1

Author Comment

by:afifosh
ID: 38824127
ok ... the problem i want to now all old transaction for the products the products is a cofee machine . and each one has a unique serial number !

and room can contain 1 to many machine.

i am the owner of the company i have 4 branch .. each branch contain room  
and we have another company defined as clients ( customers ) and each company has branches and each branch has ROOM ( like kitchen , lobby )

my company branch room is like ( stock , repair , new , old )
maybe i should add for each machine a variable condition ( new , old ) for machines ( products )

first i should install all machines in my company branches in stock room !
than i should assign each machine to another company branch with specific location ( room number or name )

i have different type of movement :
- installation ( from my branch room stock to the desired destination )
- movement ( form room to another one and may be the movement will be from company to another or between branches )
- withdrawal ( from current position to my stores ) ( maybe when i move the machine to repair or to stock )
-demonstration ( maybe i move the machine from stock to a new customers for testing. )

- reservation ?? i want to reserve a machine for a client ? how ?!!

and each machine should have a owner  the owner should be fixed bcz it's refer fo the branch owner .. ( maintenance services )

and than each machine. when i do a search .. i should now the customer who own it..
and in the same time when any transaction occurs i should have a log for all transaction
0
 
LVL 1

Author Comment

by:afifosh
ID: 38824140
i want to make something like this

http://forums.mysql.com/read.php?125,397037,397037

i have only one products .. not products with sub products i want to track each machine..
0
 
LVL 7

Accepted Solution

by:
karunamoorthy earned 500 total points
ID: 38839439
You can have product/machine table which describes the machineid/no, machine name, condition(new/old) and other machine related attributes.

Products(Productid, productname,product_condition, etc....)

You can have movements table to have purpose of movement

Movements(movementid, movenent_purpose)
for eg.
move1, installation
move2, movement
move3, withdrawal
move4, demonstration


You can have transaction/history table to track movements

History(productid,movementid,fromlocationid,tolocationid,movementdate)

With this you can able to track movement of products and its further related reports.

I hope you have something extra now.

I will give you a complete table design if you post further comments..
0
 
LVL 1

Author Comment

by:afifosh
ID: 38841739
this is a good implementation but  we have some issue to solve first i have my own company . and my company has two or three branch  
step 1 i should upload the machine to each branch

Products(Productid, ProductType,ProductSerialNumber,ProductsAgeDate)
from the products date i can know if the machine is old or new referring to the difference between product Agedate..

anyway i make new table movement
Movements(movementid, movenent_purpose)
for eg.
move1, installation
move2, movement
move3, withdrawal
move4, demonstration

my company has many clients and each client has branches and each branches has locations
each products should be located to a branchid withl locationd id i have already do this
Clients ( clienid , clientname )
company ( companyid, cpname , clientid )
branch ( branchid,branchname , companyid )
locations ( locid , locaname , branchid )

first all machine should be stored in my company branch and i will create a location name
STORE to store all machines on it..
than i should make the movement of machine . from my stock to  another company first movement is installation in client branch

History(productid,movementid,fromlocationid,tolocationid,movementdate)

the first problem how i can know the owner of machines?

view machines
machineid  machinetype  machineserialnumber  status = ( taken , available , reserved , repairing) , owner ( branchid )

and i want to store all old ..

who i can implement reservation .. of machine to a client ?
bcz i want to know how many machine is available or in repair..

my own idea is to create in my company a two new locaiton  
1 - stock ( to store all machines )
2- reserved
3 repair
4 defected

maybe i can reduce the creation of this location by make a status of each machine ..
by the way the firs step the machine should be uploaded and  installed in room stock !
for the main owner branchid my company !!!!!!

bcz i have another table assign work order so i should store the parent branch who own the machines and another field to store the branchid and locid of the new loccation

by the way in reservation the machine should be related to a clientid not branch ??
and the implementation fo changing between location  i should care if i change the location i should change the branchid ...

and in my company branches i want to transfer machines between branch stock location
??? the idea is to add move5 transfer ( will be only btwn my company branche)

hope to give me ur database model :) thank for you advice and i am waiting you :)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now