Should I choose Natural or Relational Subforms?
From the Inside Sesame Help Desk
I know that Sesame offers me a huge advantage over the flat file structure I used in Q&A, and I have read the articles about natural and relational linking of subforms. I know I can see multiple databases on one screen and see items that are related to each other while looking at a single record. What I don't know is which is better, nor do I really understand them. Can you help a technically challenged person with this dilemma?
Maureen
Actually there is no clear answer as to which is better. It depends on what you are going to do with your data. Let's look at the rules first. They're rather simple. In a relational link you need to have a unique matching piece of data - a linking key value - in a field in both the parent and subform records. So much for the rules!
Now let's look at the data entry conditions.
In relationally-linked subforms you can enter the data either in subform mode (when you have the parent record open and you are also looking at the subform) or in standalone mode (when you enter data in the subform as if it were an independent database. Either way you must enter the matching link data in the subform record.
In naturally linked subforms you must enter the data in subform mode. You do not need to do anything to make it link. Sesame handles that internally and automatically. Note: It's always a good idea to include a field that could be matched if you want to do some data manipulation at a later date. (See the past few issues of Inside Sesame, along with the "Converting Q&A Line-Items Databases" article in this issue for more on this).
Did that clear it all up for you? I didn't think so but the rest will. On to the usage part of the equation.
As an example, if you were tracking and displaying all the players on a professional sports team you'd probably want to opt for the relational model. Because of the way players move around the leagues and are constantly switching teams you'd want their records easily movable. If the key were the team name, trading a player from the Tampa Rays to the Philadelphia Phillies would be as simple as changing the team name on his record. The next time you opened the Phillies record he would appear on the roster there and would have disappeared from the Rays record. But, if you were tracking a client's invoices you'd never want to move them to another customer's record. You'd want to use the natural link option. Once you enter the data, you cannot move a subrecord from one parent record to another. You would have to delete it from one and reenter it in another.
The paragraph above sums up your entire decision process - how you want your sub data to be related to your main data. Relational subform data is easier to manage and move between parent records, while natural subform data is fixed and permanent in regards to the parent record.
Other considerations - a natural linked database performance is inherently faster than one with relational linking. If you use relational linking you should also add an entry in the Sesame.ini file that says: SERVER PREINDEX RELATIONAL: ON. This will significantly improve the performance of relationally linked databases.