abstraction / object-oriented tables / searching across unrelated tables

[tinynumbers]

Hi. New to Filemaker (but not databases in general) so forgive me if I fumble a bit with some of the FM-specific terminology.

I have a design issue that I'm not quite sure how to best address in Filemaker:

I have two different types of contact tables in my contacts database (one table for businesses; another table for individuals who may or may not be affiliated with one or more of the businesses in the businesses table), which are almost entirely different in structure, except that they both have a notion of "location", based on an address (which is part of the table definition for each of the two contact tables - i.e. each contact table has its own specific fields where it stores an address).

One of the requirements of my solution is that we be able to do a generic search by location across both types of contacts. For instance, "tell me all the businesses and individual business contacts within 20 miles of Chicago". I've got the geo-search figured out (for one or the other, separately), but what I can't figure out is how to allow a single search to produce a combined result across the two tables.

How to I produce one search-result listing which shows both types of contacts (from the two different contact tables)?

Is this a matter of creating some sort of clever relationship between the two? Or creating some sort of "meta table" which contains lookups to the two contact tables (sort of an object-oriented abstraction, with the meta-table being the generic abstraction of "has a location")? Or...?

Any advice would be much appreciated. Thanks!

- David

[fabriceN]

Hi!,

I think your are dealing with the fact that there is no inheritance in FileMaker.

What you often end up with if you really want to modelise data 'properly' is quite an unproper solution like having multiprupose tables.

For example, you can have a table 'contacts' that describe all your your contacts, whatever their type.

you would have several "sub-tables" identified by field naming such as

IND___INDIVIDUALS

INDIVIDUAL_Name

INDIVIDUAL_DateOfBirth

ORG___ORGANISATIONS

ORGANISATION_Name

ORGANISATION_VATnumber

and 'synthesis' calculation fields such as :

_NAME = Case ( type = "ORG" ; ORGANISATION_Name ; INDIVIDUAL_Name )

It can sound quite silly and disapointing, but I have good results doing so. You can also choose to have specific data in a related table, but then finds in the "meta table" have to be made on unstored calculations, which is not ideal.

[tinynumbers]

I was just reading about "Object Influenced Design" in Filemaker, from this article:

Object-Influenced Design & Filemaker Pro

It seems to be discussing an appealing way I could approach this sort of problem I'm having. But I can't find any more information on people taking this sort of approach to Filemaker database designs.

Anyone know any more resources discussing or illustrating object-relational type designs in Filemaker?

[Fenton]

There is a method which uses a separate table for the specific fields of each "entity", with a separate central table for ALL common fields. It has been dubbed (by me) the "David Graham" method, for its creator. We talked about it in these topics:

http://www.fmforums.com/forum/showtopic.php?tid/194886/post/288910/hl/%22David+Graham%22/fromsearch/1/#288910

http://www.fmforums.com/forum/showtopic.php?tid/194695/post/295990/hl/%22David+Graham%22/fromsearch/1/#295990

The main difference between this and other "central table" methods would be that his exploits a relational feature of "Allow creation of related records" to create the central tables record, and populate its ID into both tables, upon data entry.*

It shares the limitation that much of the data is relational, therefore unstored (depending on layout context). But it is a straight relationship, not a calculation, therefore fast enough.

*Actually it could also work in the other direction, central table to outer tables. But in the case of contact management it would be from outer to central, which is more natural.

[Vaughan]

One of the requirements of my solution is that we be able to do a generic search by location across both types of contacts. For instance, "tell me all the businesses and individual business contacts within 20 miles of Chicago"

Why not put the addresses into a separate table, that is shared by both the companies and individuals. Then do the find in the address table.

[tinynumbers]

I wound up using this idea and it is working great for me so far. Thanks!

[Fenton]

Good. Let us know how it goes. It's a pretty new method. BTW, it also uses Vaughan's idea, it uses a separate Address table.