Hi all,
I've been developing a small company database for over an year now. It's my second database and I would like to avoid the mistakes that I did with the first one...
So here is what I have so far. This is a simplified data model for moving products and money. A lot of supporting tables are missing in the diagram for simplicity.
(data types in tblTransactions are wrong, I know)
To describe our operation a bit:
- we have a few suppliers which we buy products from and then sell them to our customers with a profit
- there are 3 types of products (physical goods with serial numbers, physical goods without serial numbers, and intangible products like services and software)
- physical goods with serial numbers: need to be tracked individually as Units along with their complete history (repairs, after sales services...)
- physical goods without serial numbers: need to track quantities for stock management and sales analysis (also Units)
- intangible products: there are no Units, I can simply include such a product with a Sales Order (as many times as I want) and usually don't need to order it from a supplier
- some products are not associated with a supplier, for example the services that we provide
- physical products need to be ordered from a Supplier via a Purchase Order (we don't manufacture anything)
- intangible products may or may not be ordered from a Supplier (software license yes, our service no)
- physical products need to be delivered to us in a Delivery. A Delivery must only contain items that were previously ordered from a Supplier
- there can be partial Deliveries, therefore I'm tracking Delivery quantities and plan on using the DSUM function to check if the complete order has been delivered
- when a Delivery comes to us, tangible products will be placed on stock by scanning their bar codes
- Customers place Sales Orders. We either have the products on stock and can ship immediately, or we need to order them from a supplier first. I would like to implement some sort of automation here
- Sales Orders must be:
- Invoiced (sometimes partially, same principle as with Deliveries)
- Shipped (sometimes partially, same principle as with Deliveries)
- Shipment means that tangible products are removed from stock
Now, my main problem at this time is the stock management and product tracking. In an earlier conversation here on this forum, I got a tip to use tblUnits and tblTransactions (yellow in the diagram). The idea is to track each physical product as a Unit (SerialNumber field would be NULL for products without a serial number) and the Units will be used for quantities only (meaning that a USB cable for example would come to stock as a particular Unit, but could leave the stock as another Unit, using DMIN. Intangible products are never a Unit.
Transaction is always assigned to a particular Unit (with SerialNumber). There can be several Transaction Types like:
- Received from Supplier
- Assigned to a Sales Order
- Returned for a Credit Note
- Warranty Repair
- Borrowed by a Customer
- End Of Life
- ......
The idea is that every Transaction will be created via code when a particular event happens. A potential problem that I see with this is that it will be hard to think of every possible scenario, so the integrity doesn't break after a while.
What do you think about this solution? Would there be a better option to track Units in my data model?
Or do you see any other flaws in this data model at all?
Thanks a lot.
Tomas