Universal Data Model for FileMaker
One way to approach the data structure of an inventory management system is to use concepts from the Universal Data Model (UDM). The premise behind the model is that entities that have many of the same attributes may be combined to create one table instead of multiple tables.
I think of it as analogous to relational database management system (DBMS) itself. One of the purposes of a DBMS is to reduce data redundancy. Likewise, a major purpose of the UDM is to reduce redundancy in the data structure. A sort of macro/micro redundancy plan!
Combine Like Tables
There are several areas in which this can be implemented. One is by combining the vendor (or supplier) and the customer. Both are companies with addresses, phone numbers, urls, and contacts. Therefore, it is worth reviewing the two entities to see if they can be combined into one table called Company.
Make a Master Order Table
Another area of like entities is the various order tables: Purchase Order, Estimate, Sales Order, Loan Order, Work Order, etc. These are most often made up of two tables, a header and line items (sometimes called “details”). Moreover, many of the things required in the header table and the line table are the same across the different order entities. Thus, it may be possible to combine all the orders into one table, simply called Order.
There are, of course, some caveats and the Universal Data Model for FileMaker isn’t the best solution for all situations. To find out more, go to the three part series on the Universal Data Model for FileMaker (Company, Orders, Keys) at FileMaker Inventory Resources.
Need to See an Example File?
Learn how to apply what you have learned: A FileMaker supporting demo (.fmp12) demo file is available at the link.