Data Model ideas

[Thomasso]

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

[orange]

Hi Thomasso,

I'm attaching this link that describes the general approach we used to vet/test data models before committing to a physical design.

We can not see the flaws without knowing the business and business rules and testing same with sample data. You have identified many pieces and you have said you made mistakes during the development of the first database.

Treat the data model build and vet exercise as a journey more than a single event.

You can mock up parts of the design in a prototype test repeat cycle with Access to better understand the intricacies of some of the more complex rules.

Get some meaningful test data (good and bad) and exercise the model. Repeat, adjust and confirm. The easiest spot in the whole development cycle to identify and fix an issue is in the design phase. Changes to structure get costly as you get to physical database in operation mode.

What tool did you use to build the model?

Also: Here is a link to youtube video on a Stock Management Application. There are newer free samples from the company Software-Matters that may offer some insight on some aspects of design.

Good luck.

[Thomasso]

Yes it makes sense to do it like that. Do you have any opinion about the Units/Transactions approach in general?

I used Moqups - https://moqups.com/ - very powerful tool for visualization.

[orange]

Can't tell you with confidence about your units/transactions. I'd mock up a few samples and see.

You may get some ideas from this older thread (especially the comments from Allen Browne)

I found it while searching for a design example that relates quotes, sales and invoices. Not exactly your situation but somewhat analogous.

Here is a draft model of the where the "things are related" concept. Statement is often considered the supertype and quote, order and invoice are subtypes.