I have a little trouble following your structure. But one thing you mention "... more entities to deal with, each of which will have it's own table and a bunch of 'data' tables. " Entities would not have their own table. There might be a field to identify "entities".
Thanks for your reponse TKn. I meant 'entity' as in Entity-Relationship model: http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model. I was trying to distinguish on the one hand between things like 'customer' 'product' 'order' (and their respective tables), and on the other things like 'phone numbers' 'address' and 'emails.' On my Contacts layout, which is in the context of a table occurence based on my Contacts Table, I do not have any fields for telephone number. Rather, I have a portal which points to records in a table called PhoneNumbers. This way a contact can have 1 or 100 numbers and I can list them all in a compact place (a portal). I have taken this approach with phone numbers, email addresses, address, websites, notes, and customer preferences. That's six tables (and therefore at least 6 table occurences). All have foreign keys that are filled with the Contact records primary key. All records in these 6 tables are created via the portals on the contact's record.
My question is: will this approach get me into performance trouble down the road when i've got around, say, 50,000 records? (I'm interested both in a case where the database is hosted on a server running filemaker server, and >10 users on a peer-to-peer network). If so, would I be better off having a single table that holds phone numbers, address, emails, etc..... In that case, I'd still display these things in portals on the customers record, but they would be 6 filtered portals from one table, rather that 6 unfiltered portals from 6 tables. I'd very much like to go the 6 table approach, but I'm worried about future performance. The only calculations I anticipate with these tables us one aggregate calculation apiece to show the total numbers of phone numbers, emails, etc... on each customer record. I hope that clarifies the issue, and I appreciate your attention.
I have considered this design. But for my part, only Phone was needed. In one, I had companies as the parent, with multiple contact name-phone-email in a separate table. However, I never had the 50,000 parent records to determine if it slowed it down. Thanks for the clarification. Sorry I am not able to help better.
A few of my tables in one of the DB's that I manage now have several million records each. The number of relationships defined in your database should not have a significant negative effect on your system performance. On the contrary, it can actually improve performance--especially when the alternative might be a portal with a filter expression.
Both relationships and portal fitlers are things that FileMaker evaluates on an "as needed" basis. you might have several thousand relationships defined in Manage | Database, but if your layout only used 3 to display the data on a given layout, it's only those three that are actually evaluated. And FileMaker, like most RDMS software, relies on "indexes" to determine which records link to which in a relationship. These indexes are continuously updated whenever you change data in your database in a way that impacts that index such as by editing data in a field, adding a new record or deleting an existing record.
By contrast, a portal filter has to be evaluated for every related record that might appear in the portal if it "passes the filter expression". Thus, if your portal relationship links to 200,000 records, there's going to be a noticeable delay getting your layout to update while FileMaker runs through all of those records determining which should be visible in the portal. Make this a portal that includes data from a field that the user modifies to bring up different groups of data in that portal and the delays can easily make the layout unusable.
And there are other considerations that can magnify the performance "hit" for any given layout design. If you host your database from a computer outside your local network (such as from a remote hosting service), the slower data transmission rates will make everything less responsive. In similar manner, the slower CPUs and limited memory of an iOS device using FileMaker Go, will also greatly increase the delays you might encounter.
So in general, more "hard coded" relationships in Manage | Database | Relationships will be faster that reducing the number of those relationships by using a filtered portal in place of those multiple relationships.
But the trade off is that some results that you can produce with a filtered portal can be difficult to impossible to do at the relationship level.
Thanks Phil! I really appreciate the detailed response.