Jump to content

Multiple occurrences of a single table (tiered and multi-dimensional) vs. multiple tables


This topic is 4394 days old. Please don't post here. Open a new topic instead.

Recommended Posts

I'm at the point where I have 89 unique tables in a database I'm designing for a research study and I'm presented with an interesting conundrum...

The information below is borowed from the amazing article White Paper For FMP Novices courtesy of Foundation Design Systems, and after reading it, I'm curious as to the forums opinions about the pros and cons of using multiple table occurrences based on a single original table vs. using multiple tables in a FMP 11 solution. What are the main advantages to doing this? What are the main disadvantages? Are there certain situations where I'd never want to do this? Is using multiple occurrences (in this context) just a vestigial practice derived from pre FMP 7 days?

Note...I initially became interested in "multi-dimensional" tables as a way to potentially reduce the clutter of my database's table section

Tiered Tables (Multipurpose Tables)

Tiered or multi-purpose tables were more necessary in versions of FileMaker prior to 7, but are nonetheless still quite useful, and no doubt someone will find still more innova- tive uses for them in the future.

They are simply a way to turn one table into two or more tables. Let’s look at a simple example:

Again we’ll use a hypothetical contacts database. Let’s say that for each contact, you need to store a few phone numbers, some mailing addresses and a couple of email ad- dresses. The traditional approach is to create three related tables; one for phones, one for mailing addresses and one for email addresses. But you know you will never have a great many contacts, and it seems a waste to have three tables for so little related information.

You create one multi-purpose table and build three relationships (TO’s) to it from your contacts table. All of the fields you would have put into three separate related tables are put into one multi-purpose table. The multi-purpose table has three foreign key fields and three relationships from the contacts table, one each for phones, etc. These relationships and their portals in the contacts table will behave exactly like relationships to separate tables. FileMaker cannot tell the difference and you have saved a lot of space and time.


Multi-Dimensional Tables

Note: The terms tiered, multipurpose, and multi-dimensional tables are of my own in- vention. These are concepts that to my knowledge are known to a very tiny handful of FMP developers indeed and I have yet to see anyone put labels on them. I apologize if there is in fact proper terminology of which I am unaware for these concepts (and would appreciate a heads-up if that is the case). Otherwise, consider them named.

In a tiered or multipurpose table a parent table has multiple relationships to a single child table, causing the child table to act as several tables in one. In a multi-dimensional table, all the relationships involve just one table. It is similar to the tiered table concept, except that the parent and child tables are all the same, single table, instead of two sepa- rate tables. This is very useful for things like hierarchical portals and other techniques. Again, this is not a tutorial on the subject, but simply a brief description so that you know the concept exists and can determine when you might need such a tool.

In a multi-dimensional table there is a separate foreign key for each dimensional level, such that a parent record in the table can have several child records in the same table. Each child record can in turn have several grandchild records and each of those, several great-grandchild records, etc.

In the illustration below, all four of the table occurrences shown represent exactly the same OneTable table. This way, four (or as many as you like) dimensions of data can all exist in just one table. Using this technique, it is actually possible (though not necessarily a good idea) to build an entire solution with just a single table.


Link to comment
Share on other sites

I don't know your situation, so I can only speak in general:

The "multipurpose table" is a well established technique - see, for example:


It is best employed when the data types are relatively similar in attributes.

The "multi-dimensional tables" make no sense to me as presented. However, it is not unusual to use a single table for data that is recursively hierarchical. An example would be a table of Employees, where an employee may supervise a number of "child" employees. All of these, from the CEO to the parking attendant, are first and foremost employees (with name, address, DOB, etc.) so they belong in the same base table. Any other approach would require as many tables as there are hierarchy levels - and any changes in the hierarchy, or in the level of a specific employee would become a nightmare.

This is also the flaw in the parent -< child -< grandchild -< great-grandchild concept presented here. The model should allow any number of hierarchy levels, without adding more and more TOs to the graph. That's where recursive hierarchy comes into play, and it is implemented using only three TOs.

  • Like 1
Link to comment
Share on other sites

Thanks so much for your advice, as always, its really helpful.

I haven't gotten the chance to check out the links yet, but from your answer it seems that, all other things being equal (and barring situations where a hierarchy needs to be flexible a-la managers/employees) having a significant number of tables isn't a problem (and might even be preferred in most situations) in a well planned solution.

Have I gotten it right?

Link to comment
Share on other sites

IMHO, you should always have as many tables as you have entities. There can be no clear-cut answer to the question what is an entity - it depends on the purpose of your solution. In most cases, 'people' are entity and 'gender' is an attribute of 'people' - but in some cases it may be necessary to view 'men' and 'women' as separate entities.

Sometimes the decision can be quite arbitrary, e.g. demoting 'address', 'phone' and 'e-mail' to a 'type' attribute of a common 'contact methods' entity.

OTOH, when it comes to TOs, you should use as few as possible - but no less than that.

  • Like 1
Link to comment
Share on other sites

This topic is 4394 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.