How to create a DEFINITIONs record, for each record in multiple tables.

[dmontano]

Will try my best to explain what I need, and hopefully someone out there can help me determine the table schema to support it.

 

Imagine there are three tables: ANIMAL, PLANT, DEFINITION

Assume ANIMAL and PLANT must remain as two separate tables and cannot be combined into one.

 

Each record in ANIMAL may have one or more DEFINITION records.

Each record in PLANT may have one or more DEFINITION records.

 

USER SCENARIO

User creates a new record in ANIMAL;

Users enters the word "HORSE" in the ANIMAL TYPE field on the record;

User creates a DEFINITION in the definition portal on ANIMAL layout by typing a few words;

User creates a second DEFINITION in the definition portal on ANIMAL layout by typing a few words;

User creates a third DEFINITION in the definition portal on ANIMAL layout by typing a few words;

 

The same User Scenario is applicable to PLANTs.

 

MY CHALLENGE

The above scenario indicates that ANIMALs and PLANTs can each have one or more DEFINITIONs. The creation of the three tables is straight-forward, and connecting the relationship between ANIMAL and DEFINITION is simple. My challenge is how to connect PLANT to the DEFINITION table as well.

 

Method A:

I can connect ANIMAL to DEFINITION as such: ANIMAL::animal_pk = DEFINITION::animal_fk

I can connect PLANT to DEFINITION as such: PLANT::plant_pk = DEFINITION::plant_fk

 

Doing so would create two separate fields to hold the foreign key values in the DEFINITION table and one would always be empty. As soon as another ENTITY requires the same DEFINITION capability, I will have a third foreign key field in DEFINITIONS. This seems ugly.

 

 

Method B:

I can rename DEFINITION table to ANIMAL DEFINITION; and create a fourth table: PLANT DEFINITION.

I can connect ANIMAL to ANIMAL DEFINITION as such: ANIMAL::animal_pk = ANIMAL DEFINITION::animal_fk

I can connect PLANT to PLANT DEFINITION as such: PLANT::plant_pk = PLANT DEFINITION::plant_fk

 

Doing so would create basically two separate tables that hold in essence the same type of information: DEFINITIONS.

As soon as another ENTITY comes along like ROCK, it would require a ROCK DEFINITION table. This seems ugly

 

 

Method C:

I can "abstractly" name the foreign key field in the DEFINITION table to "source_table_fk", and link ANIMAL, PLANT, ROCK, or whatever to this field as a means to establish the relationship.

I can connect ANIMAL to DEFINITION as such: ANIMAL::animal_pk = DEFINITION::source_table_fk

I can connect PLANT to DEFINITION as such: PLANT::plant_pk = DEFINITION::source_table_fk

 

Doing so prevents me from sorting DEFINITION by ENTITY TYPE (ANIMAL, PLANT, ROCK, etc). Maybe adding a TYPE field on ANIMAL, PLANT, ROCK and bring that over would provide the filtering value needed to do such a thing. The Primary Key values in the ANIMAL, PLAY, ROCK tables would have to be prefixed or similar to avoid key collision in DEFINITION. This seems a little dodgy.

 

If anyone can comment based on experience with this sort of conundrum would be appreciated.

 

Thanks,
David

 

 

 

 

 

[doughemi]

Create a global field animal_key = "animal" in your ANIMAL table.  Create a Type field in the DEFINITION table, with either a value list or, better, a scripted Set Field to enter the correct type.

 

Make the relationship

 

ANIMAL::animal_pk = DEFINITION::fk  <--only one fk field needed in DEFINITION, regardless of the number of parent tables

ANIMAL::animal_key = DEFINITION::type

 

Likewise for the PLANT table.

[beckeeper]

 

Method C:

I can "abstractly" name the foreign key field in the DEFINITION table to "source_table_fk", and link ANIMAL, PLANT, ROCK, or whatever to this field as a means to establish the relationship.

I can connect ANIMAL to DEFINITION as such: ANIMAL::animal_pk = DEFINITION::source_table_fk

I can connect PLANT to DEFINITION as such: PLANT::plant_pk = DEFINITION::source_table_fk

 

Doing so prevents me from sorting DEFINITION by ENTITY TYPE (ANIMAL, PLANT, ROCK, etc). Maybe adding a TYPE field on ANIMAL, PLANT, ROCK and bring that over would provide the filtering value needed to do such a thing. The Primary Key values in the ANIMAL, PLAY, ROCK tables would have to be prefixed or similar to avoid key collision in DEFINITION. This seems a little dodgy.

Okay, I'm a Filemaker newb, but I've done a lot of database work in the past, and this actually seems simple to me. If I have this right, you need to be able to filter your definitions, and one Animal can have many definitions, as one Plant can have many Defintions, and maybe in the future also Rocks (Minerals? I feel like we're playing 20 Questions ).

Anyway, we need to filter all the definitions, by whether they are an Animal or a Plant or a Rock, if I follow you correctly. I think you actually had it in Method C, but are over complicating it. You could add another table to the database, Entity Types, and all it has to do is hold "Animal", "Plant", "Rock" and whatever comes in the future. Use this table to create a value list, which can be used when creating the initial new entry in another field in the Animal, Plant or Rock table (Entity Type perhaps). I see it in my head like so:

Tables and fields:

Animals

animal_pk

Animal Name

Entity Type

Plants

plant_pk

Plant Name

Entity Type

Defintions

entity_fk

Definition Text

And then the unrelated table, to hold the values for the value list to be used when creating a new Entity, be it an Animal, Plant, etc.:

Entity Types

Entity Type

Alternately, you could easily prefix your animal_pk or plant_pk serial...I know Filemaker defaults to 1, but you can use letters also, so you could set your serials to be ANI1, ANI2, etc. and PLA1, PLA2....

I guess it just depends on how you want things to run. Me, I would add the Entity Type table, as it seems like it would be less work on the user side.

I hope this helps, or at least gets you thinking in the right direction!

[bcooney]

Well, I for one want to question the stipulation that Plants and Animals cannot be in the same table. Is it an interface requirement? If so, that can be solved with a sub/super type structure. See this thread.

 

Edit: Ah, you started that discussion, lol! So, why are Plants and Animals in different tables? Is this hypothetical or real data, bcs I don't have patience for hypothetical. As many on the thread feel, I design for real-world use. Also, I avoid abstraction and would have two foreign keys.

[Fitch]

I'd also vote for method C. I think the "type" field is a fine way to relate the records, and is the one I'd probably use. You mentioned key prefixes as a "dodgy" alternative, but IMO it can also be an effective method. A third option would be to use UIDs for your plants and animals. The UID method requires nothing additional, however the first two methods do give you an easy way to isolate all records of a given type should the need arise.

[dmontano]

First off…

 

Thanks doughemi, beckeeper, bcooney, and fitch. I really do appreciate your willingness to lend a hand.

 

My comments or answers to some questions posed…

 

@ doughemi - the one field to hold the FK in definitions (regardless of how many parent tables I connect to) has been noted - thanks.

 

@ beckeeper - yes minerals would be better. The Entity Types, however, are not ANIMAL, PLANT, ROCK - they are just placeholders for the real ENTITY TYPEs that I am dealing with. The ENTITY TYPEs I am dealing with are DOMAIN TYPEs and DOMAIN MEMBERs and are abstracts. I felt I would have a difficult time explaining what they are and their purpose - which would get in the way of getting my point across.

 

@ bcooney - Yes, I abstracted. Had to. Not to be coy, just the subject of what a DOMAIN TYPE and DOMAIN MEMBERs are would probably come up and I would have a hard time explaining what the heck they are. If I could EVER get a SUPER/SUBTYPE structure functioning - I would be a VERY happy filemaker wannabe. That single structure (SUPER/SUB) alludes my capability. I have seen a few implementations and they are beyond my comprehension - or are not very smooth in interface. Not that they should be as they are typically proof-of-concepts, but I don't have the chops to make them smooth out of ignorance of the mechanisms that make it work. That's another topic.

 

@ fitch - I mention dodgy because I would have in essence a smart key instead of a dumb key. Kinda like a composite key where two fields are joined: serial and the TYPE to create uniqueness. The type field you suggest makes sense. As for the UID - I think that would be the ideal solution to deal with my (self-imposed) belief that the prefix keys are dodgy. I have no experience that prefix keys are bad and I actually do it all the time - go figure! Psychosis on my part? Perhaps.

 

Anyways - really appreciate y'alls help.

[Tom R.]

Also, I avoid abstraction and would have two foreign keys.

 

I would be interested to hear your reasons for avoiding abstraction, and using to foreign key fields instead of a single abstracted foreign key field. I'm using a single foreign key field to support relations to multiple tables in a project I'm working on now. It is working fine so far, but if there are pitfalls or drawbacks I'd like to know about them. 

 

Thanks,

 

Tom

[bcooney]

Tom, describe your scenario. Perhaps, I was too fast to condemn abstraction and don't want to do so with such a broad brush. After all, to many it's the way to multi-purpose relationships and scripts. However, when I see a reliance on naming conventions (tables, layouts, etc) and the use of Set Field by calculation that is dependent on naming conventions, I back away fast. Just too fragile. Even FM's internal IDs are subject to change. That is not a trade-off that I think is worth making.

 

So, abstraction must be achieved carefully and not at a cost of reliability, imho.

 

Reporting comes to mind as something whose complexity increases as abstraction increases. But, it's best to really judge each setup on its own.