I am setting up a sustainability assessment database and am new to FMP, and although I have worked through many of the main basic requirements, I am not sure on the best way to set things up. I have 6 main assessment categories arising from economic, environment and social issues. These categories have a collective 91 sub-categories. The first one, EC for economic, has 9. The second (EN for environment) has 34 and the rest are social indicators. The first sub-category in EC, EC01, has 10 indicators requiring data entry. Every record needs to be related to a client, who will want a report on their current level of sustainability and future reports on how they have improved, based on the action plans I provide, based on the assessment data. This means I also need to relate the fields in the assessment to another table with actions designed to improve the client’s sustainability. This section of the database will grow with my experience.
Most categories have 9-12 sub-categories (except EN) and each sub-category has several items requiring data entry. This leads to 91 sub-categories and hundreds of fields requiring data entry. Should I make tables for each sub-category, so EC01 with its 10 fields would be in one table? Or can I cascade down from the main categories? Either way, I will need to place the data records relating the particular sub-category items and the client in a layout. Perhaps a join table between clients and sub-categories/categories? If so, are the records driven by the data entry in the sub-category with a selected client foreign key?
Sounds like you would have client, assessment, category and category items tables at a minimum. If the category items' data entry will be similar or close enough, they can share a table easily enough. This would allow you to add more items at any time without programming.