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 equipmentManufacturer, equipmentModel, equipmentOwner fields, but fields such as micFrequency or 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 equipmentManufacturer, equipmentOwner, purchaseDate, etc. as well as two pointers for sub-categories, subKey and subTable. These last two would then be used to retrieve the data for specific sub-categories such as Microphone, Display, Camera, and so on.

Asset is a parent/umbrella table for all equipment, grey tables contain data only specific to a sub-type.

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)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.