Hi,

How would you conceptualize a zero-to-many self-join?

In my case, I need to create a database that contains cities.

Some cities have boroughs others none and some boroughs have areas and others none.

Do I create three distinct tables or I create a single table with a parent field in it?

This is a very tricky subject. What you have essentially is a location which can be comprised of other locations, which in turn can be comprised of still more locations. It's a recursive data structure and they're easy to implement but difficult to work with in FMP, typically because layouts aren't dynamic.

Depending on what you want to do with these locations, you can either create specific tables for each location type, or you can use a single table and configure a recursive relationship between the table and itself.

Using some of the techniques available elsewhere you can present recursive data structures as a collapsable tree structure.

To this structure, I will attach a street table which can belong to one of the three entities (city, borough or area).

The maximum number of recursion is 3.

I already developed the structure with 3 different tables a few years ago, but the problem is that when a city was by itself, I needed to replicate the record in the borough table and the area table so the street would be attached to the right city. In FM 8, it was quite awkward for the user. Now with FM10, it could be different with the event handling.