I'm trying to make the database system point of sale, however I am confused between the entity and product inventory entity. What are the differences between product and inventory?
I know that the inventory should control the amount of product available .... but i have all that in products.
product code
name
description
cost
unit price
Subcategory code
brand code
amount available
Minimum quantity for rehearing
state
tax code
weight
amount wholesales
wholesales price
perishable
due date
creation date
upgrade date
what i should have in inventory? I have researched and according to what I read I need to have the product, the description, the quantity, purchase price, sale price, profit or gain and date of the transactions. But almost everything is in the Products table, what should I do?
I think you can store the inventory in the products table. There will certainly be transaction tables for purchases for the products and sales and even adjusting records (when items get count and the number differs from what's stored in the database), but you can easily work with the stock stored in the production table itself, thus not having to scan the whole database and sum up all purchases and sales and corrections every time (and never being able to delete old transaction data from the database, as that would invalidate the calculations).
However there are reasons to have stock stored in an inventory table instead. For instance if you want to store different statusses, e.g. you have 100 pieces in store plus twenty just arrived and still unchecked. Or you have a store with goods plus a warehouse housing additional stock. Or you have charges (different model numbers for example for a slightly altered product) which you offer as the same product, but still want to know how many old and how many new ones are in stock. And so on.
So make your mind up, if you want to store additional data with product stock, which would result in an 1:n relation instead of 1:1 which you have now.