Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 Warehouse DB

Author  Topic 

MoodyIT
Starting Member

11 Posts

Posted - 2014-08-21 : 03:35:14
Hello,
I'm trying to develop a program that helps me with managing warehouse stuff… warehouse working process includes two main things
1- Receiving materials depending on paper document includes the following fields
* Material information like: name, code, description, unit
*supplier name of material
*Date
*Quantity of each received material
*Notes
*Warehouse employee name
2- Delivering materials also depending of paper document which includes the following fields
*it includes the same fields of the previous document with a little difference
Please note that each paper document can includes more than one record (I mean by one paper I can receive 5 materials ex.)
My first analytical study leads me to the following database diagram
Employee table used if warehouse employee deliver an item to one of the company employee.
Is this the right way or do you have any suggestion?
By the way I'm trying by this design to start using Barcode system for input and output so is this the right way to do that ?
Thanks in advance

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-21 : 07:52:42
You've only mentioned one table (Employee) but I have a feeling that more are required. I suspect you need tables for:

Employees (for both Warehouse and Company employees, assuming the warehouse belongs to the company)
Suppliers
Materials
Orders (for materials coming into the warehouse from suppliers)
Customers (Can also be used for internal customers (e.g. other employees)

Probably other tables will be needed as you develop your design.
Go to Top of Page

MoodyIT
Starting Member

11 Posts

Posted - 2014-08-24 : 02:07:44
Thanks gbirtton for your quick response
actually i already build those previous tables which you did mention... i tried to attach a photo for my DB diagram but i couldn't.
my DB consists of the following tables
Store (if i have more than one store at my company
Category (which used to categorize company materials)
Materials
Orders
Material-Orders (which used to break many to many relationship between material and orders and contain information about the order including materials with their bar-codes )
supplier
Employee (include personal information of employees)
the point which i didn't get it is how to link material with employee or customer? should i link it with material-orders tables? if that's true it should be done by creating material-employee table to break many to many relationship.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-24 : 08:47:28
Material would likely be linked to an order which in turn is linked to a customer. I would expect many to many between orders and materials. Think about it.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-24 : 08:49:12
Is it an OLTP-database or a DW-database?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

MoodyIT
Starting Member

11 Posts

Posted - 2014-08-25 : 02:10:53
gbritton, i can see many to many between orders and materials, and as i mentioned in my previous reply i broke this relation by using Materials-orders table... i think that orders and customers also having many to many relationship right?

SwePeso, actually it's DW-database because i wanna use it to store data and getting some reports in addition to input and output operations which can be done by just one user (i mean i don't need many concurrent users)
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-25 : 09:10:16
orders to customers should be many to one. that is, an order wouldn't be shared by two customers, would it?
Go to Top of Page

MoodyIT
Starting Member

11 Posts

Posted - 2014-08-26 : 02:56:45
yup that's right :) ... thanks for helping me with some issues... i think that now i'm having a clear idea about how my database should be ... thank you :)
Go to Top of Page

MoodyIT
Starting Member

11 Posts

Posted - 2014-08-26 : 03:28:43
back again :) ... if i want to work with bar-coding for each item .. where should i store it? in materials-order table?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-26 : 08:04:03
I suppose that the bar code is a property of the material, not the order. If so, I would store in the materials table, not the orders table
Go to Top of Page

MoodyIT
Starting Member

11 Posts

Posted - 2014-08-27 : 01:54:19
i have many kinds of orders ... like input materials to warehouse, output, and order materials from warehouse
let's assume that we want to receive materials from outside ... i already have static materials in my DB like printers, PCs, whatever ... now when the warehouse man receives the new materials he's going to choose the type of new materials from (drop down list) and then declare the specific properties of that material like its bar-code.
i don't know if my idea is clear ... but in general i have general properties for materials which stored in DB ... and specific properties which need to be stored at order time.
i don't know how to attach a photo to let you see my DB diagram.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-27 : 08:04:55
Your idea is clear. Your design is not (yet!) What will happen when "the warehouse man receives the new materials"? Will those materials join the other "static materials in my DB"? If so, put the barcode and other information on the item there; if not, what will you do with the information on the new materials? That will help determine where things should go. e.g. perhaps a separate materials table just for temporary items associated with an order. I still would not put that info in the orders table.
Go to Top of Page

MoodyIT
Starting Member

11 Posts

Posted - 2014-08-28 : 01:56:47
Let's say that i received 10 HP [p1005] printers if i did store these information in Product table i will have 10 records in my product table each one have its own bar-code... but when i have static product that called HP Printer in product table i will have just one record and in Order-details i will have 10 records with their bar-codes and other details... what do you think about that?
i'm still confused about how my design should be
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-28 : 07:53:55
By "static product that called HP Printer" do you mean a generic class of printers from HP? If so, it would be better to have a code associated with each item that has a corresponding entry in a look up table. e.g. suppose you have a lookup table:


create table lookup (code char(3), desc varchar(50))
insert into table lookup (code, desc)
select 'HPP', 'Hewlett Packard Printer'


Then in your product table insert a [code] column and set it to 'HPP' for each of those 10 HP printers along with their barcodes. When an order is received, the barcodes get scanned and stored in the product table along with the other details that are specific to the printer (e.g. purchase date, purchase price, probably other stuff)

Don't store a generic 'HP Printer' in your product table.
Go to Top of Page

MoodyIT
Starting Member

11 Posts

Posted - 2014-09-08 : 07:06:46
Sorry it's long time since my last reply ... thank you for your reply ... so i have to create tables to categorize my products and then dealing with product details at product entity ... right?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-09-08 : 10:14:03
That's what I would do, yes
Go to Top of Page

MoodyIT
Starting Member

11 Posts

Posted - 2014-09-09 : 02:18:08
will let's assume that categorizing starts as the following;
categories: Office supplies ----> Stationery ----> Papers ----> A4 paper
Code: 1 1 2 1
buy that way i can know that 1121 is A4 product
but how to implement that in my DB ... i mean what's the best method to design my entity to store the previous hierarchy? should i have one table? or many tables?
i really appreciate your help :)
Go to Top of Page
   

- Advertisement -