|
Your continued generosity and support of FMForums is greatly appreciated. |
tinynumbers
novice
Posts: 16
Loc: Chicago, IL
Post Rank (AVG):
FMP: 9 Advanced
OS: Mac OS X Leopard
Skill: Intermediate
 Tweet This Post!
|
 Welcome tinynumbers's first post!
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
master
Posts: 309
Loc: Brussels, Belgium
Post Rank (AVG):
FMP: 11 Advanced
OS: Cross Platform
Member: Platinum
Skill: Certified Trainer
Certified:
    
FNC
Tweet This Post!
|
In response to tinynumbers
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
novice
Posts: 16
Loc: Chicago, IL
Post Rank (AVG):
FMP: 9 Advanced
OS: Mac OS X Leopard
Skill: Intermediate
Tweet This Post!
|
This is tinynumbers's second post.
In response to fabriceN
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
Carpal Tunnel
Posts: 4986
Loc: San Diego, CA
Post Rank (AVG):
FMP: 10 Advanced
OS: Mac OS X Leopard
Member: TechNet, FBA
Skill: Advance
 FMPug
Tweet This Post!
|
In response to tinynumbers
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/19...
http://www.fmforums.com/forum/showtopic.php?tid/19...
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
Mostly Harmless
Posts: 8310
Loc: Sydney, Australia
Post Rank (AVG):
FMP: 9 Advanced
OS: Mac OS X Leopard
Member: TechNet
Skill: Advanced
Certified:
 
Tweet This Post!
|
In response to tinynumbers
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.
| Vaughan Bromfield
Sydney, Australia
Please post questions to the Forum, not directly to me. Back-up your files before making changes!
Whenever I hear the term "popular culture" I reach for my Iridium Q-36 Space Modulator. |
|
tinynumbers
novice
Posts: 16
Loc: Chicago, IL
Post Rank (AVG):
FMP: 9 Advanced
OS: Mac OS X Leopard
Skill: Intermediate
Tweet This Post!
|
This is tinynumbers's third post.
In response to Fenton
I wound up using this idea and it is working great for me so far. Thanks!
|
|
Your continued generosity and support of FMForums is greatly appreciated. |
Fenton
Carpal Tunnel
Posts: 4986
Loc: San Diego, CA
Post Rank (AVG):
FMP: 10 Advanced
OS: Mac OS X Leopard
Member: TechNet, FBA
Skill: Advance
FMPug
Tweet This Post!
|
In response to tinynumbers
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.
|
|
|
