inventory system mssql database desgin

Posted on 2013-01-27
Medium Priority
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...
Question by:AFIF JABADO
  • 3
  • 2

Expert Comment

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

Author Comment

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

Author Comment

ID: 38824140
i want to make something like this


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

Accepted Solution

karunamoorthy earned 1500 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


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..

Author Comment

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


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 :)

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

600 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