Generalization Hierarchy in FileMaker

[teka]

I am wondering how other developers have implemented a generalization hierarchy structure in FileMaker. I am referring to supertype/subtype generalization which is often used in object-oriented systems and also known as classes.

There are normally three methods to map this in SQL:

1) Use one table for the supertype with separate tables for the subtypes (AKA Separate tables method - a compromise method, which can be complex to comprehend)

2) One single table with fields for both subtypes, where the fields are null for some subtypes without certain subtype attributes (AKA Single table null fields method - probably easiest but messy due to all the nulls)

3) A single table with a separate table for all of the detailed attributes listed as records linked to the main table record ID (AKA single table with normalized detail attribute records - best structure, extensible without changing script code, more work to add all the types, may have performance issues in some cases).

Thanks for your input!

Theo

[Søren Dyhr]

Alright I've only seen the last one, and use it myself - like:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

...and should be honest to admit I don't know how to implement the others, other than it seems to be like your 2) is in the vicinity of:

http://www.fmcollective.com/2006/12/postdesign_cauc.html

Please do expand for us ignorants!, finally is it falling under your 1) when developers swear to this:

http://www.fmforums.com/forum/showpost.php?post/201104/

I post this reply, simply becasue I'm currious!!

--sd

[teka]

Add Subtype/Supertype capability

Request Description

Subtype/Supertype capability is becoming essential for modern relational databases. This is sometimes erroneously referred to as Object/relational, in fact Codd proposed these extensions to his original relational model back in 1979, See: Codd - Relational Model of Tasmania.

Oracle, MySQL5, DB2, and most other major SQL databases support these constructs. Implementing this in Filemaker is tedious, perilous, and differentially executed by every developer. In most cases developers create redundant tables which creates the problem of synchronizing validation and other constraints between subtype tables. Then there is the additional problem of referencing the supertype, with separate subtype tables this can get ugly in Filemaker, including problems with record locking.

Background info.......

Generalization Hierarchies

http://www.utexas.edu/its/windows/database/datamodeling/dm/erintro.html

A generalization hierarchy is a form of abstraction that specifies that two or more entities that share common attributes can be generalized into a higher level entity type called a supertype or generic entity. The lower-level of entities become the subtype, or categories, to the supertype. Subtypes are dependent entities.

Generalization occurs when two or more entities represent categories of the same real-world object. For example, Wages_Employees and Classified_Employees represent categories of the same entity, Employees. In this example, Employees would be the supertype; Wages_Employees and Classified_Employees would be the subtypes.

Subtypes can be either mutually exclusive (disjoint) or overlapping (inclusive). A mutually exclusive category is when an entity instance can be in only one category. The above example is a mutually exclusive category. An employee can either be wages or classified but not both. An overlapping category is when an entity instance may be in two or more subtypes. An example would be a person who works for a university could also be a student at that same university. The completeness constraint requires that all instances of the subtype be represented in the supertype.

Generalization hierarchies can be nested. That is, a subtype of one hierarchy can be a supertype of another. The level of nesting is limited only by the constraint of simplicity. Subtype entities may be the parent entity in a relationship but not the child.

See also:

http://www.cs.ucd.ie/staff/acater/home/comp4002/rmt.pdf

How might this request be used?

Implement entities that can share sets of attributes, cutting down on the complexity of the model.