Opinion for table strcture (1 Viewer)

[john_gringo]

Hi, I'm building a new DB for my company.
It will be handling inventory, Stock in and Stock out
Is it better to have two tables for products that came In and out or just one with Quantity calculation?

Thanks in advance

 

[theDBguy]

Have you seen this?

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com

 

[Pat Hartman]

To answer the question, stock in and stock out are the same thing. They are movements of stock. There are additional stock transactions also and you will eventually come to realize that. Once you do, you will understand the wisdom of using a single table and how it will simplify the process.

 

[john_gringo]

Have you seen this?

Allen Browne - Inventory Control: Quantity on Hand

How do calculate the quantity-on-hand for inventory applications, rather than rely on a stored value.

allenbrowne.com

To answer the question, stock in and stock out are the same thing. They are movements of stock. There are additional stock transactions also and you will eventually come to realize that. Once you do, you will understand the wisdom of using a single table and how it will simplify the process.

Thank you both....
just read it
@Pat Hartman
One table then will be for the inventory and one for the transactions and there will be calculations for in and out, etc.....if I understand well?
I assume that you mean returns by additional transactions.

 

[john_gringo]

..........and what about Customers, Suppliers, Clients and Transporters, can I have a single table for all of them instead of one for each?

 

[theDBguy]

..........and what about Customers, Suppliers, Clients and Transporters, can I have a single table for all of them instead of one for each?

What's the difference between clients and customers?

 

[Pat Hartman]

I'm pretty sure that Allen's preferred method doesn't store inventory. It is calculated on the fly. You have a table to define products and a table to record inventory transactions. You sum the transactions to determine inventory on hand.

That is the simplistic answer. We have no idea what type of inventory you are tracking or what special rules you might have or what volume we are talking about. If you have thousands of items and thousands of transactions every week, summing the transactions could get slow in a hurry. Therefore, there are reasons for keeping an inventory table to record on hand quantities. However, I would always use transactions to record inventory movement since if you don't, you have no way of reconciling errors.

Do you have items that expire? Do you have items that have serial numbers? Do you have to handle "kits" (sub assemblies)?

We can only offer guidance and best practices choices.

 

[Pat Hartman]

and what about Customers, Suppliers, Clients and Transporters, can I have a single table for all of them instead of one for each?

Normally I would. Especially if entities can have multiple roles. You don't want Customer Acme to have a different address than Supplier Acme if they should be the same.

 

[john_gringo]

What's the difference between clients and customers?

Ok...I will store products (not mine) for customers that will be delivered by suppliers. We will store them and afterwards, we will distribute them, half by a transportation company and others by private transportation vehicles to the Customer Client (recipients).
We will invoice customers for our services and will produce a delivery note for every transportation combining many products into one order.

 

[theDBguy]

Ok...I will store products (not mine) for customers that will be delivered by suppliers. We will store them and afterwards, we will distribute them, half by a transportation company and others by private transportation vehicles to the Customer Client (recipients).
We will invoice customers for our services and will produce a delivery note for every transportation combining many products into one order.

So, are you saying you have customers, and your customers have clients?

 

[john_gringo]

I'm pretty sure that Allen's preferred method doesn't store inventory. It is calculated on the fly. You have a table to define products and a table to record inventory transactions. You sum the transactions to determine inventory on hand.

That is the simplistic answer. We have no idea what type of inventory you are tracking or what special rules you might have or what volume we are talking about. If you have thousands of items and thousands of transactions every week, summing the transactions could get slow in a hurry. Therefore, there are reasons for keeping an inventory table to record on hand quantities. However, I would always use transactions to record inventory movement since if you don't, you have no way of reconciling errors.

Do you have items that expire? Do you have items that have serial numbers? Do you have to handle "kits" (sub assemblies)?

We can only offer guidance and best practices choices.

So if I understand well, I should have inventory, inventory details and transactions tables and calculate on the fly and by each transaction update for example the table!product!quantity field to record stock on hand?
Yes, we have items that expire as also a serial number for all the products and bach number also. No, I don't have Kits.

 

[john_gringo]

So, are you saying you have customers, and your customers have clients?

yeap, we are storing and distributing other's products, a warehouse.

 

[Pat Hartman]

So if I understand well, I should have inventory, inventory details and transactions tables and calculate on the fly and by each transaction update for example the table!product!quantity field to record stock on hand?

NO. We don't know what you need. We don't have any idea what problem you are trying to solve. Start by trying to understand the methods outlined in Allen's post. Then create a table schema that contains the data you need to track and we'll look at it.

 

[mabino79]

i suggest you can post a flowchart snapshot or Excel format u to show the flow of data & output.

 

[john_gringo]

Ok....let me prepare it...