Currently working on designing a FileMaker application to keep track of A/V equipment in a company. The application needs to be able to display each room/location with the A/V equipment that is installed there, who owns the equipment, and when each item was installed, among other features.
An issue I am trying to solve is that the data itself is currently organized in a table that contains common columns but also contains columns specific only to sub-types of equipment. All rows use the
equipmentOwner fields, but fields such as
physicalAddress are not used for all of those rows.
My current solution is to create sub-tables or sub-types, something that I've found works well in SQL but have not found much on how to do it for Filemaker. I have a parent table
Asset which contains all of the common fields such as
purchaseDate, etc. as well as two pointers for sub-categories,
subTable. These last two would then be used to retrieve the data for specific sub-categories such as
Camera, and so on.
Something about this approach seems a bit off to me and I can't exactly pinpoint what it is. Is there a simpler or better way of approaching this problem? Can it be normalized more? (e.g. just using the primary key from
Assets or getting read of those sub-tables all together and keeping one master table)