Design issues with many to many relationships and large numbers of fields
January 26, 2016 at 11:15 am #1882
I need to design a database describing ‘services’, with six main categories of services, and each main category has variations in the form of sub-categories, ranging in number from 9 up to 34, totalling 91 sub-categories in all under the 6 main categories.
Each of those 91 sub-categories has a number of fields requiring data input, both qualitative, for example pricing, and qualitative, for example quality of service. What would be the best set up for the user to choose particular services to enter data on? I keep coming back to the need to create tables for each of the 91 categories.
Customers use the same services over time, requiring a many to many relationship with data fields.
I am thinking a join table between the sub-categories and the customers, but this means 91 tables, or can the 91 sub-categories somehow be housed in one table, or perhaps the initial 6 main categories, and be linked to the customer?
All this data will eventually produce reports to show trends, based on initial data inputs.
Most importantly, where would the data be entered? Perhaps in the services table with the customer foreign key in place? Which table to use to input data is confusing me currently. Maybe a portal in the join table if one is required?
I am hoping I am on the right track.
January 27, 2016 at 2:28 pm #1911
A lot of questions! I hope I can help!
Firstly, I believe you are thinking about far too many tables.
Quick Definition: An entity is a coherent, distinct type of thing that your system will keep records of. In FileMaker, and most other database programs, entities are called data tables. When starting your database it is common to begin with identifying things that could potentially be an entity. A simple way to do this is to identify all of the nouns in a system description.
Reading your description I think there might be four entities in your solution; Customers, Services, Categories, Subcategories.
Quick Definition: An attribute is an individual characteristic of an entity that is worthwhile or relevant to keep record of. Try not to have any duplicate attributes, where it can be avoided, in multiple entities. For example, ‘price’ might be an attribute of the entity ‘Services’.
I am also a little unsure of what it is exactly that you are trying to keep record of? Do you have a system description?
January 28, 2016 at 1:04 am #1913
Thanks for your interest Sam,
Here is my situation in full:
I am setting up a sustainability assessment database and am new to FMP, and I am not sure on the best way to set things up. What I hope to achieve is work on site with businesses, probably with an iPad, and input data based on Indicator fields relevant to each area of sustainability. I am basing the Indicator fields requiring data input on the Global Reporting Initiative (GRI) model for reporting sustainability. This standardises my reporting structure.
My aim is to stay with a client for the long-run and provide an ongoing, cost-effective service to incrementally bring about changes towards sustainability, so assessments and reports will be ongoing and comparable.
The 6 main GRI categories arise from economic, environment and social issues. These are EC, EN, HR, LA, PR, SO. These categories have a collective 91 sub-categories. The first main category, EC for economic, has 9 sub-categories. The second main category (EN for environment) has 34 and the last 4 categories are based on social indicators, whose sub-categories range in number from 9-16. Combined, they form the 91 sub-categories.
The first sub-category in EC main category, is EC01. It has 10 indicator fields requiring data entry. These fields currently live in the EC01 GRI Indicators table. Each of the 91 sub-categories has a number of indicators requiring data entry. For practical purposes, I have a set of Indicator questions, which will probably be radial yes/no/na and perhaps a field for notes, to assess sustainability practices and to assist in producing action plans for improving sustainability.
Client_Indicators is a join table to show client and record indicator data.
The GRI codes table has all 91 GRI codes as records, to show description and relevance for the GRI codes using value lists.
So far, I have experimented with relating EC01 GRI Indicators table to make sure the join table works (my first attempt!). It does ☺
Rather than 91 tables to join to, I would rather join to one table where I can select the sub-category Indicators from. This is where I am stuck.
To tidy up the tables set up, the most efficient way would be to display the fields for entry associated with each sub-category eg EC01 (with its 10 EC01 GRI Indicator fields plus question fields and notes).
In order to contain all this information on one Client_Indicator layout/record, I thought of using tabs for each of the 6 main categories, EC etc, then using slides for each sub-category, like EC01 etc. Each slide would house the sub-category Indicators fields for entry and the questions fields with the radials for yes/no/na and notes. Getting these fields onto a layout for entry has me looking at portals relating to a table, which brings me back to lots of tables – not good.
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, which has been based on the previous 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, as the sub-categories will record ‘researched’ and ‘actioned’ action options.
I hope this makes some sort of sense.
You must be logged in to reply to this topic.