Dynamic Portal - Display Data from Multiple Tables

[laurend]

I created a database to track permits by building. Each permit type is stored in its own table. The only link between tables is the Building Name which is the physical location where the permit exists.

Tables:
v_Buildings (fields include building name, ID, building status, address, city, state, zipcode)
Air (ID, Building Name, Expiration Date, Days_to_Expiration, Type, etc...)
Water
Food
Elevator
...9 permit types in all, and I will add more in the future

Each of these tables has the same fields in common:
BuildingName
PermitExpirationDate
PermitOwner
Days_to_Expiration

I created a Dashboard layout with the idea that users could select a building name from a drop-down menu and then see all of the permits for that particular building along with their expiration date and the permit owner's name. I am just baffled as to how to do this across multiple tables. I have looked at SQL, Join tables, portals etc...., and I cannot figure out how to aggregate all of this information into one view for users.  I cannot even seem to figure out what table should be used for the Dashboard layout. I would love to hear from the community the best and hopefully scalable approach for designing this layout.  Thanks in advance from a novice user.

 

[comment]

15 minutes ago, laurend said:

Each permit type is stored in its own table.

IMHO, that is a mistake. You should have one table for all permits, and indicate the permit's type in a Type field. Then the presented problem will cease to be one.

--
P.S. The Permits table should use a BuildingID field as the matchfield to the Buildings table, not BuildingName (which could potentially change).

 

Edited August 22, 2016 by comment

[laurend]

Comment - Thank you for providing your input. I almost designed the database that way, but the Air table alone has another 50 fields that are unique to the Air permit only. This is the case, albeit at a smaller number, for each of the other permit types too. I was concerned about having over 300 fields in one permit table whereby on a few of the fields would be used per record. The building table is linked by the ID and not the building name. Given this additional information, would you still recommend one massive table to store all permit data? 

[comment]

2 minutes ago, laurend said:

iven this additional information, would you still recommend one massive table to store all permit data? 

Actually, yes.

Strictly speaking, the "correct" solution would be to have a single supertype Permits table, with only the fields that are common to all permit types, and 9 satellite subtype tables, one for each permit type, holding the fields that are unique to each type, related in a one-to-one relationship to the master Permits table. However, this structure is not easy to implement in Filemaker - and the penalty for having the "extra" fields in the master table is not that great.

Another option you may consider is the EAV model. Here you would have a Permits table, a table of Attributes (~ 300 records, each associated with a Type) and a join table of Values. This too is more complicated to set up than the single table, but easier than the supertype/subtype model, IMHO.

 

 

[laurend]

Comment - Thank you again. I think I knew deep down that doing the supertype/subtype table structure would be the best way to go, but I was daunted by the change to the backend without an expert's opinion. In reality, I don't have that many records, and since I already have the subtype tables (e.g. air, water), it seems simpler to create the super permit table and migrate a handful of fields per permit type instead of migrating all of it. This just makes total sense for doing a Dashboard item too.

For the Dashboard, would you base it on the super permits table and then use a table layout with a script trigger to allow the users to filter the list by building? Or, would you create a portal based upon the Dashboard table (no records in that table) and add the super permits table as a portal?

[comment]

Before anything, I would try and resist the urge to have a dashboard at all. Dashboards are very popular now, but they can be expensive in terms of performance and I have my doubts regarding their usefulness.

If I had to do it, I would probably use a layout of some utility table, and use a global BuildingID field to link to the Permits table, to show the selected building's permits in a portal.

[laurend]

Comment - I agree with you on the Dashboard concept. I put one into my last solution, and the performance is terrible. I am not familiar with a "utility" chart and didn't find much searching for that online. Is it just a dummy table with just the BuildingID as a global field? Do you then base your layout on that instead? How exactly would that differ from using a Dashboard? My dashboard table has several global fields and several calculation fields as well as the usual Modification/Timestamp stuff.

Thank you again for your help. I am nearly done updating my super table and adjusting the layouts accordingly. 

[comment]

By "utility table" I meant any table that isn't used for "real" data. If you want to have a dedicated Dashboard table for this, that's fine.