Self join Relationship or New Table?

[Dimitrios Fkiaras]

Hello,

In my solution, I have a "Personell" table which includes about 400 records.

Personell is distinguished, according to its specialty to :

• mechanic
• electrician
• driver

Some fields in this table hold attributes that are common to all records(Personell) like:

• DOB
• Address
• Phone number
• Email et.c.

On the other hand, each specialty has its own unique attributes. For example "drivers" have:

• licence ID
• licence type
• Total hours behind wheel et.c.

 

Should I put the fields that hold specialty-unique attributes, to my main table ?

OR

Put them on  a related table that holds only the _pk of each Person , and make a script to populate

the new table each time I have a new entry on the old?

 

Thanks!

[comment]

Both solutions are valid and which one you choose depends on details that we don't have - and to a large extent on your personal preference.

Implementing the second solution (a.k.a the supertype/subtype model) in Filemaker is not trivial, and if the number of the specialty-specific fields is not very large, you may well prefer to keep them all in the same table.

There's also a third alternative in the form of an entity-value-attribute table: https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

 

[Dimitrios Fkiaras]

Thanks!

[Dimitrios Fkiaras]

What are the things to have in mind if implementing the supertype/subtype model?

I  hope this is not a too general question

 

[comment]

1 hour ago, Dimitrios Fkiaras said:

I  hope this is not a too general question

I am afraid so. But do a search for supertype - this has been discussed before.

[Lee Smith]

Try this Google Search

site:fmforums.com supertype

HTH

Lee