I can not find the way to build an asociative entity.

[ggallegose]

I was using Ms Sqlserver, Oracle, MySql and Ms Access. Now I need to use FM Pro 8.5 but i have not found something like DDL in FM Pro. I need to work with business rules embedded in data model, then, i need to make constrains, multifield primary key´s and stuffs like that.

Please, can anybody help me? Is there any book or manual? Thanks in advance.

[Vaughan]

I've not got an SQL background, but my guess is that most of what you ask about can be done with field-level validation and/or access privileges.

With FileMaker, the data model is integrated with the interface. Many things such as enforcement of business rule are done through interface design. (IMHO sometimes they are not done very well using just interface design and field validation and access privileges should also be used to really enforce them.)

[ggallegose]

Thank you for your opinion. It makes me re think about it. May be i am trying to do something for FM 8.5 Pro is not designed. Perhaps i must look in the FM Server version.

[comment]

The data model is the same for Server as it is for the other versions. I am not familiar with SQL either, but I believe Filemaker's Define Database... is roughly equivalent to SQL's DDL. If you'd present a more specific question/example, we would know better where to point you.

[ggallegose]

Thank you. I´ll try to explain. I have three entities (tables), for instance: GeographicPlace, Salesman and Product. Each one of this entities have an Primary Key and must be related through an associative entity called SalesJournal. The Primary Key of this last entity (SalesJournal) must be formed by the PK of each one of the first three entities plus a date field (it relates the GeographicPlace, SalesMan and Product. In other words, each instance is a sale event), and that means that this composed PK ,must be UNIQUE. In my context, this rule must be in the back end because a lot of TI people can use the information and therefore, some of them will use Microsoft tools, another group will work with Java and so on.

Finally, i have not found how to declare this relationship. Thanks in advance.

[comment]

In Filemaker, you would call an associative entity a 'join table'. Also, a table does not have a PK as such. You decide what is your PK for each relationship (although it is good practice to have an auto-entered serial number field for this).

It follows that having designated (mentally) a combination of fields as the "primary key", Filemaker will not automatically enforce uniqueness of such combination. You need to do this yourself.

In your example, you would probably define a text field with auto-entered concatenation of the 3 FK's and the date, and validate this field as unique. This could be augmented by a "dwindling value list" to assist the user in data entry - your example is not a good one for this, but let's say in an invoice you couldn't choose the same product twice.

Two related threads:

http://www.fmforums.com/forum/showpost.php?post/163868/

http://www.fmforums.com/forum/showpost.php?post/204677/

[ggallegose]

Thank you very much. I will let you know my advances in this topic.

[ggallegose]

I mean, thank all of you.