Separation Model for Many To Many Relationship

[RalphL]

I have completed a demo of Many To Many relationship using the separation model described by Collen Hammersley and Wendy T. King in Migration Foundations and Methodologies. There are 3 files: (1)Data, (2)Business Rules & Reports and (3) Interface. Data has 3 tables, Business Rules has 4 tables and Interface has no tables. The fourth table in Business Rules is Globals.

I use the Troi Text Plug-in in this database. It will not run if you do not have the plug-in installed. I use this plug-in to maintain the uniqueness of the records in the join tables. The plug-in is available at http://www.troi.com.

I am looking forward to your comments and suggestions.

[Logixx]

The suggestion I'd modestly give is to get rid of plug-ins (except your own, if any).

If you want a steady solution, rely on FMP and no third-parties. You never know what will happen with your favourite plug-in at the next FMP upgrade, as light as it could be.

Probably most users think the same way, and this explains you haven't been replied so far???

[RalphL]

I strongly support the use of plug-ins. They are not unique to FileMaker. Many programs use third party plug-ins. They allow the user to adapt or modify the program to meet their needs. I have been using the Troi Text Plug-in since FMP 4.

Other than plug-ins what did you think of files?

[Logixx]

My policy is:

1/ if you can do something with FMP, don't do it with a plug-in, just shake your brains

2/ if you cannot do something with FMP, shake again, maybe you can

3/ if you cannot do something with FMP and have your brains shaken that much they're upside down - forget that useless little thing

As to your files, sorry I can't use them since I don't install plug-ins.

[slytle]

I would really like to see the DB, because I'm about to do the same thing, but I don't use plug-ins either.... Anyone out there have an example that ANYONE can open?

.sly

[RalphL]

You can down load the plug-in and use it for a short time without buying it. I use the plug-in to limit a value list to those items which have not been used. This insures the uniquness of the concatenated key in the join table.

[RalphL]

I have modified the demo files to not require the Troi Text Plug-in. I was able to achive the same results using a != relationship to get the value list of unused supplements.

[RalphL]

I have discovered an error of omission in the last set of files. When a new product is created the the value list for the supplements is empty. I had not checked this until yesterday when tring to apply this to another thread.

To fix this I changed the field definition of "Unused Spplements" in the Table "Product_BR" in the File "Many To Many BR" to "0

[egor]

I have found this dowload incredibly helpful with an update to fmp7 I have been tackling over the last few months.

Am I right in thinking that all the main relationships between files are made to the "business logic" version of each table, and that this version simply "pulls' the live info out of the data table through a relationship based on the serial number?

if so, I have a question for you. On some of my tables I have relationships based on several different serial numbers. Would I need to keep those serial numbers on both logic and data tables, or could I leave all that relationship stuff in the business logic file.

I hope this makes sense and thanks very much for the model - it has kept me up long into the night for several weeks now!

[RalphL]

Welcome to the forum.

Some relationships are in the 'business logic" but all are in the "interface". Check the relationship graph for each of the files to see the differences.

I use a one to one relationship between the "data" and "business logic" tables in the "business logic" file. There are no relationships in the data table file.

I generate the serial numbers in the "data" tables. I then place a copy of the serial number in the "business logic" tables. This done when a new record is created in a data table. I also have a script in the "business logic" file that will update from the records in the "data" file. The purpose of this script is to allow replacement of the "business logic" without changing the data.

I am glad that someone has found these files useful and I hope you haven't lost too much sleep.

[egor]

Very good. What is your suggestion on the following?

I have a join table called "Role" and this connects the table "Person" with the table "Organisation". The only pure data field in the "Role" file is called "Description". Everything else is serial numbers or calculation fields.

Would you still suggest I make a separate logic table is cases like this?

[egor]

Oops, should have looked more closely at your example. Your "Supplement" and "Supplement_BR" tables have exactly that scenario. The "Supplement_Name" field in the "Supplement" table is the only data field. Which means your answer to my question was probably going to be yes!

[RalphL]

If you are going to use the separation model then my answer is yes. Don't forget the Supplement table has a Supplement ID field. Because I did that in the sample doesn't mean that is necessarily the way to do it. The sample had just the fields necessary to make it work. In the join file you have 2 foreign key fields.

[egor]

Hi Ralph

Are there any particular rules when accessing the pure data tables? For example, I want to make a relationship with my "ActivityData" table in order to get the information from it. My first inclination is to access the "Activity_BR" table, but that doesn't have the hard data in it. Should I therefore make TWO relationships, one with "Activity_BR" and then link it with the "ActivityData"? If yes, does that as a rule of thumb I should connect to my data tables through the logic tables whenever I want to make additional relationships?

[RalphL]

If you look at the relationship graph in the both the Interface file & BR file you will see that I have a relationship from the BR table to the Data table for Product, Supplement & Product_Supplement . If this is what you are proposing then yes. In FMP 7 you can go many relationships deep. In your example you would then use the TO "ActivityData" to access the pure data.

[egor]

The implication of this is that I double the number of table occurences on my relationship graph. I can see the benefits, but it seems that all the relationships (and I have got a fair number of them) have to go through the logic tables, with the data tables feeding into each. True?

[RalphL]

Not necessarily. Look at the relationship graph for Interface. I have a relationship from Product_BR to Product_Supplement_Data (Create Edit) and a relationship to Supplement_Data (Unused). Both of these bypassed their BR counterparts.

I think you will have to determine this for each relationship.

I am glad that you are taking such an interest in this.

[egor]

Yep, I am having great fun with this!

So what about filter keys? For example, I have previously used calculations to concatenate various attribute fields in the data file. I use this field to create a relationship that can be "searched" by global fields on the other end. eg, I might have two global fields in one file "size" and "type" which are joined by a calculation. This field is related to a calculation field in the data file which also joins the "size" and "type" fields.

These filter keys often combine three or four fields. Do I have to ship all these fields into the Business Logic file in order to create a calculation that can be indexed?

Hope this makes sense!

[RalphL]

In FMP7 you don't need these calculations. You can make a relationship that requires both size and type fields to match. Look at the relationship in the Interface file between Product_BR and Product_Supplement Create Edit. This relationship has:

Product_BR::Product_Serial_Number = Product_Supplement Create Edit::Product_Serial_Number

AND

Product_BR::g_Supplement_Serial_Number = Product_Supplement Create Edit::Supplement_Serial_Number

This eliminated the calculated filter keys.

Relationships in FMP7 are realy powerful, they open up a whole new world in database design.

[egor]

Yes, thanks, I'm slowly coming to terms with that. In your demo the Product_Supplement Create Edit relationship is directly accessing the Product_Supplement data table. So this is one of the examples where it is a table occurrence accessing the real data. I'll work with that in mind.

Incidentally, in your last post you indicated two conditions in the relationship, but when I looked at the demo it only had one. Have I got the right version?

[RalphL]

I made some changes and that may not be in last one set of files I posted.

[egor]

I

[RalphL]

I don't think that you want to repeat a lot of data. That could be a nightmare. I can not visualize your TOG. You can have more than one TO in the TOG. The BR tables are mostly calculation and summary fields.

[egor]

Ralph

I am still finding your example files very helpful.

Your post of 8 December describes the relationship in the Interface file between

[RalphL]

See the newer version I have attached. I made some changes which I never posted.

[egor]

Ralph

Thanks for that. I can see that your script pastes in the Product id and selected Supplement id into the new Product_Supplement record.

I have simplified all my relationships so I can reference the new business rules tables. There is one area I am still puzzling on

[RalphL]

Do you mean not making relationships in the Data File between data tables? I don't have any because there is no reason for them. These tables contain only data, no calculations, so they don't use relationships.

I use some relationships in the Business Rules file and more in the Interface file.

These are my personal preferences, they are not hard and fast rules that can not be broken.

[bsacco]

Well, my experience with plug-ins are not good.

I think it's not good becuase of the nature and structure of FM plug-ins...

The company selling the plug-in can only sell it for problably less than $75, any higher it starts to rival the cost of the application.

Let's face it, FM pro support sucks, and even if you pay for it to "Get expert help" it sucks even more because you quickly realize you know just as much as the expert FM pro hired to answer the phone on their behalf.

FMPro changes so much from version to version and contempt they have for the average user increases with each release.

So, if you are looking to buy a plug-in and then get support from...remember you paid about $55 for the product which barely covers the cost of producing it because they don't sell enough volume to make enough profit....then, remember you are asking them to spend 30 minutes on the phone troubleshooting your implementation of their product....it's a losing proposition...

[Ender]

bsacco,

This doesn't seem to be a new insight into the Separation Model, nor does it ask a followup question about Ralph's topic.

Maybe you should put your rant in your own thread (and delete this post.)

[bsacco]

sure feel free to delete...i would but don't know how...

[laker_42]

Would anybody be able to post RalphL's most updated sample file as a zip? I don't have access to a Mac.

Thanks!

John

[RalphL]

Here it is zipped. You could get a copy of suffit for your windows machine that could unstuff an .sit file.

SeparationManytoManyfolder4B.zip

[laker_42]

Thanks Ralph!