Need Advice

[smtazulislam]

Hey, I would like to create a Professional POS for Pharmacy shop. below all of the table almost complete.
01. tblPharmacyInfo - Completed
02. tblCustomers - Completed
03. tblSuppliers - Completed
04. tblProducts
05. tblPurchases
06. tblPurchaseDetails
07. tblOrders
08. tblOrderDetails
09. tblExpense - Completed
10. tblSales
11. tblStaff - Completed
12. tblShippers - Completed
13. Invoice- Need suggest to create this table.

Now, I have some questions to related these table.
Where / Which table should I adding these item - MinStockLevel, MaxStockLevel, ProductionDate, ProductExpiryDate, ShippingCost, TaxesAmount, Payment.

Profit - get it by Query Or need add in any table.
Payment - Need a table or Optional (There most sales by Cash 95% and 5% - Visa/Mada card. )
- And Purchase Payment (By 90% Transfer to Supplier Bank Account, 5% Cash And 5% Installment Payment.)

 

[The_Doc_Man]

The min/max stock level belong with tblProducts. If you have issues with expiration of product, however, you have a non-traditional inventory problem such that the usual "sum of transactions" method of inventory won't work. You would have to track age of product as an individual product property, which PERHAPS means that "Products" has two layers. One layer describes the product, stock levels, and other GENERAL issues. A child table would have to track individual product lots that have an expiration date because you need to track the age of each lot.

Shipping cost seems likely to be something that is ALSO a property of a product lot as opposed to a product overall property. (Particularly if you have - and use - multiple sources for any product.) You might need an extra junction table between products and shippers for this sort of tracking.
Tax amounts SEEM TO ME to be a per-product item and thus MIGHT be appropriate in tblProduct - unless they have multiple sources such that they are imported from places that have different tariffs.

Payment probably belongs with the invoice. I don't think you can finish the invoice, though, until you decide some of the issues related to aging product lifetimes. Profit will almost certainly be a sum of transactions for purchases, sales, and shrinkage (due to aging products). Plus... employee salaries if that is at all a possible concern.

I suspect that once you clean up some of the issues above, what's left would be simpler and maybe you will see some of the answers amid what is left after the mental clutter is removed. But heck, you can come back and ask again for what is left, after all.

 

[CJ_London]

looks like you are missing a transactions table - this would contain the FK to the products table, the transaction type, transaction date, ProductionDate, ProductExpiryDate, quantity and FK to the order table. Also perhaps price/cost

the transaction type would perhaps be purchase, sale, writeoff, return to supplier, return from customer

I had a client who had some 1000 pharmacies around the country plus a main warehouse and 4 or 5 regional warehouses. One of the issues was product description - 'tablets' could be reduced to tab or tabs, 'capsules' to cap/caps/caplets immediately spring to mind but there were others. The product needed to specify among other things a name, dosage and 'form' - i.e. tab or cap. In addition there were commercial names - two suppliers supplying 75mg aspirin may have different names as well as their own product code. Products with the same name and dosage could be interchangeable as being identical, but when they had different forms they might be considered identical - it was for the pharmacist to decide.

So you may need another table to identify comparable products.

 

[smtazulislam]

The min/max stock level belong with tblProducts. If you have issues with expiration of product, however, you have a non-traditional inventory problem such that the usual "sum of transactions" method of inventory won't work. You would have to track age of product as an individual product property, which PERHAPS means that "Products" has two layers. One layer describes the product, stock levels, and other GENERAL issues. A child table would have to track individual product lots that have an expiration date because you need to track the age of each lot.

StockLevel, TAX Amount, belongs to Products table. So Expiration date also put to Product table. it is not a large shop. Someone also advised me that the Expiration date instead of a Stock table.

Shipping cost seems likely to be something that is ALSO a property of a product lot as opposed to a product overall property. (Particularly if you have - and use - multiple sources for any product.) You might need an extra junction table between products and shippers for this sort of tracking.

Here Maybe need to clear it. It's not the Purchase shipping cost. Customer Shipping cost. Some products will be sent to courier that cost will be bear by the customer. We collect Airway Bill and will pay the courier end of the month.
I think it belongs to Invoices table. But need your suggestions and review this point.

Payment probably belongs with the invoice. I don't think you can finish the invoice, though, until you decide some of the issues related to aging product lifetimes. Profit will almost certainly be a sum of transactions for purchases, sales, and shrinkage (due to aging products). Plus... employee salaries if that is at all a possible concern.

In the table INVOICE:
InvoiceID-PK
InvoiceNo - Numbers
ProductID-FK
CustomerID- FK
InvDate-Date/time
Qty-Numbers
ShippingCost- Currency
PreviousDue-Currency
TaxPercent-Pecent-18-2-2
TaxAmount-Currency
TotalAmount-Currency
Discount-Currency
GrandTotal-Currency
PaymentType-ShortText
PaymentAmount-Currency
CurrectDue-Currency


You mention the employee's salary. It's put in the Expense Table.

About the payment to Shipping and Supplier, how can entry each Transaction? You knew that Post #1 mentioned how to payment to Suppliers.

 

[smtazulislam]

looks like you are missing a transactions table - this would contain the FK to the products table, the transaction type, transaction date, ProductionDate, ProductExpiryDate, quantity and FK to the order table. Also perhaps price/cost

Yes, I think it must add. Any Suggest
TransactionID
ProductID
OrderID
TransactionType - purchase/sale/return to supplier/return from customer
TransactionDate
QTy
Price

Why do you want Productiondate + ExpiryDate in the Transaction table? Cause it was already in Product Table

I had a client who had some 1000 pharmacies around the country plus a main warehouse and 4 or 5 regional warehouses. One of the issues was product description - 'tablets' could be reduced to tab or tabs, 'capsules' to cap/caps/caplets immediately spring to mind but there were others. The product needed to specify among other things a name, dosage and 'form' - i.e. tab or cap. In addition there were commercial names - two suppliers supplying 75mg aspirin may have different names as well as their own product code. Products with the same name and dosage could be interchangeable as being identical, but when they had different forms they might be considered identical - it was for the pharmacist to decide.

So you may need another table to identify comparable products.

Nice point. I need more explain it, please. some example if possible.

 

[CJ_London]

Any Suggest

depends on your app - how you process orders and sales. It could be you need a sales ID as well. It could be this table is effectively a combination of your purchasedetails and sales details tables. So you will either be linking to those detail tables or to the header tables.

You can still maintain referential integrity providing the child field is null

some example if possible

Sorry, it was a while ago - best example is the one I provided. And again, depends on your business. As far as I can remember we had table along the lines of

PK...Name....Alias....other fields
1.....A.............null
2.....B.............null
3.....C.............1 'from one manufacturer
4.....D............1 'from a different manufacturer
5....E..............2

So every product had a alias

the basic query would be something like

SELECT nz(B.PK,A.PK) as pPK, nz(B.Name,A.name) as pName
From pTable A LEFT JOIN pTable B ON A.PK=B.Alias

Same thing with the 'form' for comparison purposes - a query along the above would 'convert' all forms into a standard alias 'form' so that you could then match on the 'aliased' name, the 'aliased' form and the dosage.

Capsules and caplets were a particular problem, because they are not the same - capsules are liquid contained in a dissolvable container whilst caplets are solid like tablets. For general prescription purposes they are the same (given the same doseage), but there can be medical reasons why one is preferred over the other. Fortunately there was other data in the product profile data that helped to distinguish caps to mean caplets or capsules.

Data came in, typically around 5000 rows a day. Before being entered into the main system it would be 'washed' to ensure it conformed with the db constraints - all valid records were imported, invalid ones reported separately and there was a manual operation where someone would decide the best way of getting it into the system - typically creating another child to the alias, sometimes creating the alias. Typically there were 10-20 records a day needing review.

 

[smtazulislam]

04. tblProducts
05. tblPurchases
06. tblPurchaseDetails

systematically to inserting PRODUCT COST, AND SALES PRICE, This two fields became a query or should in a table. If your answer is table, Which table ?