How can I manage a crosstable situation

[burningman]

Hi, and sorry for the interception.

I'm studying how to deal with a crosstable as well. I've read all entries in this post with interest.

Could it be useful to use a very simple example to discuss all functions needed and if available handling methods for the cross-table problem?

I have the impression that the main question "how can I manage a crosstabel situation" are still unanswered.

I'd like to use a very simple example for this

Table a contains fields id_a (primary key) and a (some value).

Table b contains fields id_b (primary key) and b (some value).

Table axb contains id_axb (primary key), and fields id_a (foreign key), and id_b (foreign key). Field id_axb mustn't be there, but it could be useful later on for the definition of a relationship specifying e.g. the type of a certain axb link.

What I'm lokking for is an example able to handle the following functions:

Create, Read, Update, Delete a row in table a.

Create, Read, Update, Delete a row in table b.

Create, Read, Update, Delete a row in table axb.

Before able to define what every function must do I need a set of rules, e.g.:

a row in a can exist without a row in b.

a row in b can exist without a row in a.

a row in axb connects a row in a with a row in b. This row can only exist if both rows to be linked together exist in table a and in table b.

The rules define the kind of referential integrity I want to have for the db as a whole.

The rules are application dependent. So yours might be differ from mine :-)).

To create/read/update/delete a single row in each of the three tables is easy. It can be done with a simple layout for each table.

But according to the rules mentioned above this makes only sense for tables a and b, and deletion of a row in table a or table b can destroy the referential integrity of the DB, if there's one or more rows in axb containing the id (foreign key) of one of the deleted rows. If this is the case the row(s) in table axb must be deleted too.

Any ideas about the opportunities to define referential integrity in Filemaker? I'm coming from DB2 database design, so I don't know what Filemaker can handle in this area, sorry ;-).

If Filemaker is unable to handle such situations, I assume that one or more scripts are needed to offer the functions needed for this - right?

Besides this I'd like to have one layout showing me the values of all fields a in table a as a list, all fields b in table b in as a list, and all existing connections in between (content of table axb as a list).

The goal is to have a layout where I can select one or more rows in table a and one ore more rows in table b to connect or disconnect them from each other.

It would be nice too to be able to click (and highlight) any row and get displayed the related ros to this too.

Does anybody think it makes sense, to define a simple DB with three simple tables, a set of rules, and a set of general functions needed to handle this very common situation (DB, Tables, Scripts and Layouts)?

*Edit mr_vodka: Broken Off from http://fmforums.com/forum/showtopic.php?fid/32/tid/194695

Edited November 5, 2008 by Guest
Broken off from thread 194695

[comment]

I don't think your post is pertinent to this thread. Perhaps one of the moderators will split it off.

What you describe is mostly a 'meat and potatoes' join table. You can find a basic demo of this here:

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

I believe all the features you have listed are either in there or could be added with little effort.

[burningman]

Thank you for your table join example. It was useful for me to see how two tables and their associations can be managed with layouts, and I like it.

But your example does not maintain referential integrity. When I delete a record from table Organizations or from table Contacts, the association table contains an incomplete (and from my point of view wrong) row in the Affiliations table after this.

And sorry, but I do not understand the meaning of "meat and potato" - what do you mean, please?

May be that this is the wrong post/thread for this. Whenever there's a better one for this question, just let me know - no problem.

What I address is wheter there is or are filemaker patterns or methods to implement referential integrity for cross-tables based on application dependent relationship rules valid for a certain cross-table (there may be several of these in a single DB and the integrity rules could probably be different for each of them).

My main concern is to keep a certain n:m relation integer, that means correct, even when deleting rows from any table. This means the ability to implement automatic DB functions to prevent the DBs integrity based on the rules that are valid for a certain n:m link.

Am I right by assuming that Fm does not offer such functions, and that I need some own code to keep the table Affiliations consistent with the other two?

I'm looking forward to your answer with interest.

[comment]

I do not understand the meaning of "meat and potato"

I meant this is nothing new or overly complex. It's a standard way to implement a many-to-many relationship. Even a basic invoicing solution has to deal with this situation, and the concept of a join table is common to all relational databases, not just Filemaker.

BTW, the term "cross-table" is normally used to describe cross-tabulated data, and it may be a bit confusing here. I think most of us call it "join table", though "junction table", "bridge table", "map table", "link table" or "associative entity" are also valid terms.

When I delete a record from table Organizations or from table Contacts, the association table contains an incomplete (and from my point of view wrong) row in the Affiliations table after this.

This is easily solved by going into the relationships' definitions and enabling the option "Delete related records in this table when a record is deleted in the other table" on the Affiliations side.

Other measures to ensure referential integrity can be taken through field validation (e.g. foreign keys cannot be empty, etc.).

Am I right by assuming that Fm does not offer such functions, and that I need some own code to keep the table Affiliations consistent with the other two?

Filemaker offers the tools - it's up to the developer to implement them (or not). There's no master option named "Referential Integrity" that you can select and have Filemaker do all the necessary work for you.

May be that this is the wrong post/thread for this

Don't worry about it. The moderators will take care of it - or not, whatever they see fit. I only mentioned it to make clear that this is NOT continuing the previous discussion.