Schema drive design or vice versa?

[cwcrogan]

Hello, I've been out of the Filemaker world for about a decade but I'm diving back in.

I'm trying to develop a dispatch system that includes a 'run card' for each box location. A run card is basically a 4x4 table showing who responds to the box, who responds to cover, and for which level alarm. The table header is box assignment engine, box assignment ladder, cover assignment engine, cover assignment ladder; and the rows are first alarm, working fire, second alarm, third alarm. Can't wrap my head first around how to store this in the database and second how best to display. There are only sixteen values, so I don't think it would be terrible to just include those in the box table. However, I see I could split it out and have attributes for assignment, truck type, and alarm. Then how to best display? I could simply create a table using text boxes and graphics and then place the fields over them, but this seems so unFilemaker like. Perhaps a portal sorted by alarm attribute?

Would love some helpful guidance - thank you!

[comment]

Can you tell us more about the real-life objects that this is supposed to track? And also about the purpose of your tracking: are there specific views and/or reports that you need to generate? You ask:

3 hours ago, cwcrogan said:

Then how to best display?

but I would ask, regardless of the underlying schema, what form/s of display would best serve your purpose?

 

[cwcrogan]

Hi comment,

IIRC, you helped me many times years ago - and here you are again! Thank you.

I had a feeling I wasn't explaining myself that well. Every address in town is covered by a box (master box for a single address, street boxes cover many addresses). Each box has a run card associated with it. So when a box is received or struck, the dispatcher needs to see the run card (if the incident grows beyond an investigation) to know who is coming to the box or to cover. The data rarely changes (but does occasionally), so this is more for reference than anything. A typical run card may look like the attached (still working on the design). So the dispatcher will search for the box and will be able to see the run card 'table'.

Hope this makes sense.

[comment]

I am afraid it's still mostly Greek to me. In general, when you have a many-to-many relationship - such as one box has many responders and one responder can be assigned to many boxes - you resolve it by a join table. This allows you (1) to produce a report of boxes and their responders and/or responders by their boxes and (2) to add attributes to each join (for example, the type of alarm the responder is supposed to respond to).

If you don't need this, then you may get by using a simple table structure like the one you show. It may be even simpler if you have exactly 4 alarms for each box; in such case you could use a repeating field with 4 repetitions instead of 4 separate fields. This would provide a simple way to find a box by responder, regardless of which alarm is assigned to.

But you are right that this is closer to an Excel spreadsheet than to a relational database.

 

[cwcrogan]

 

I'll look into the repeating fields - that may help.

I'm sorry I didn't explain it well. I just realized I never stated this is for a fire dept (which might explain the engine and ladder). I'm probably making things much harder than it needs to be. Basically, every box has a run card table similar to the one above.

So the dispatcher receives a call reporting smoke showing from a building at 100 Main St. He/she looks up 100 Main St and sees it is covered by Box 34. He transmits box 34 and Engine 1, Engine 2, and Ladder 1 respond on the first alarm. The incident commander on scene then radios a working fire and the dispatcher needs to see the run card associated with Box 34 to see who should be responding to cover the station (a Stoneham Engine in the example above) and radios Stoneham to make sure they're enroute. The fire grows and the IC requests a second alarm. The dispatcher again refers to the run card and sees that a Reading engine, the Stoneham engine (that was covering) and a North Reading Ladder are due to respond directly to the Box (fire address) and that Saugus and Woburn are each sending an engine to cover and Lynnfield is sending their Ladder to the station as well.

As mentioned, the data in the run card 'table' seldom changes. The fields in that table will most probably never be searched or used to find a box, just referenced from the Box, so in a way they're just attributes of the box. But I think you can see how I could easily split the data to another table and each record would contain 5 fields - box foreign key, assignment, alarm, and truck type 'tags' and then the data above. But I'm beginning to realize there's no real benefit in doing so - in fact, it's probably disadvantageous. There are actually 10 alarms for each Box, but after the 3rd it's handled by someone else.

Please don't feel compelled to reply - I think I know the direction to head now. Thanks for your time comment!

 

Edited March 7, 2022 by cwcrogan