Hi Marco,
I have to alter my previous post a bit as I didn’t understand the meaning of Commesse. Google Translate came up with Orders, but as you stated Project is more appropriate.
In the way you are describing the requirements of your app, you’re actually almost there in realising the data model. A good understanding of what the requirements are is half the work.
First the Commesse and date selections:
These should result in a set of sales and purchase records filtered by the selections made. Customers and suppliers should be associated automatically. This means that every sales record should link to a data value and a Commesse value. This must be done by using a central link table (synonym: fact table).
First read your sales records. Expand this table with a key field to the calendar (_Date.KEY) and Commesse (_Commesse.KEY). Create a temporary table with your purchase records and also add the _Date.Key and _Commesse.KEY. Concatenate the two tables.
Load your Commesse table and add the key field _Commesse.KEY. Load the master calendar and add the _Date.KEY key field.
Now both purchases and sales are linked to the calendar and commesse tables.
If needed you can create a Sales Detail table and Purchase Detail table to reduce the number of fields in the central link table.
For customers and suppliers I have to make an assumption and that is:
>> Customers link only to the sales table
>> Suppliers link only to the purchase table
So add a key field to the customer table in the sales table (_Customer.KEY) and do the same for the supplier in the purchase table (_Supplier.KEY).
Please take note that a selection in Supplier means that only purchase records are associated.
I've added an example of a data model to this post.
Cheers,
Michiel
.
