Hi,

I developed a long time ago a Stock/Sales/Invoice database solution but i do not remember the kind of relationships I used.
I remember there were 3 tables including a "joint" table (SALES) in order to display a portal with all sales for a given invoice in the form INVOICE. SALES was linked to a STOCK table in order to retrieve the items from the stock directly from the portal in INVOICE.
So I think we can call that a many-to-many relationship ?
(I remember the joint table was something fundamental in Filemaker but I did not remember why exactly, maybe to build a many-to-many relationship in order to display the portal or something like that)

Issue in that solution was the data integrity also :
For example the ability to change the price of an item in the stock at a "t time" without reflecting this change to the previous registered sales But this new price is of course available for new sales. A concrete example could be "30% price less for an item during a given period".
(I remember there was a question of reciprocity of relationships also).

I try to develop now such a solution in Obj-C (Macintosh) with the new CoreData feature.
So finally my questions are : what kind of relationships are involved at the different levels of such a database ? One-to-Many ? Many-To-Many ? Unidirectional vs Bidirectional ?

Many thanks in advance for your answers.

The situation you described is a typical many-to-many Invoice -< LineItem >- Product solution. The join table is the LineItem table, joining Products to Invoices. In this way, each Invoice can have many Products, and each Product can be included on many Invoices.

Usually in Invoice solutions, it is important that once the Invoice is created, the prices and descriptions don't change. To accomplish this, you need the LineItem table to keep copies of those Product fields. In FileMaker, this is done by defining normal text or number fields in LineItem, and defining them to have an auto-entered Lookup or Calculation, drawing from the corresponding related Product fields.

Your example of "30% price less for an item during a given period" implies two additional complications. The first is where to apply the discount. Discounts could either be done on the Product side (a particular product being discounted,) or on the Invoice side (the Customer recieves a discount for all items THEY purchase.) Once you know which of these to use, then you must either build that into the structure so the discounted price is the source of the Lookup, or add scripting to set the discounted price for the specified product(s). The second complication is "given period". The simplified way would be to manually adjust the Price of the Product in the Product table as it changes. But for a larger system, or if you want to have better tracking of prices over time, you could add a Price table, and have that related to Product. Keeping date ranges for each record in the Price table, you could then relate your LineItem to Price by ProductID and Date, and get the correct Price for the current date.

In FM7 & FM8, the relationships are bidirectional as long as the keys on both sides are indexed. In previous versions, each relationship has to be defined in its own file, but you can see both ways if both keys are indexed. The bigger advantage in FM7/8 is the ability to see data multiple tables away, eliminating much of the need for Lookups. In the case of Invoices however, Lookup fields are needed to preserve the history.

That's it !
Now I remember these "lookup" fields !
A price table for the product is a very good idea...
Many thanks for this crystal clear explanation, I think it will be useful for everyone also.