| 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 things1- 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 name2- Delivering materials also depending of paper document which includes the following fields*it includes the same fields of the previous document with a little differencePlease 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 diagramEmployee 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)SuppliersMaterialsOrders (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. |
 |
|
|
MoodyIT
Starting Member
11 Posts |
Posted - 2014-08-24 : 02:07:44
|
| Thanks gbirtton for your quick responseactually 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 companyCategory (which used to categorize company materials)MaterialsOrdersMaterial-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 )supplierEmployee (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. |
|
|
|
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. |
|
|
|
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 |
|
|
|
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) |
|
|
|
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? |
|
|
|
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 :) |
|
|
|
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? |
|
|
|
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 |
|
|
|
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 warehouselet'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. |
|
|
|
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. |
|
|
|
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 |
|
|
|
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. |
|
|
|
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? |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-08 : 10:14:03
|
| That's what I would do, yes |
|
|
|
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 paperCode: 1 1 2 1buy that way i can know that 1121 is A4 productbut 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 :) |
|
|
|
|
Warehouse DB