How to manage hundreds of fields and their related records

[Jo_In_Oz]

Is there a limit to how many fields a table should have?

My solution requires the management of 6 categories. Each category has up to 34 sub-categories, although most have 9-12, and each sub-category has several items requiring data entry. This leads to 91 sub-categories and hundreds of fields requiring data entry. The records per sub-category require relationships to corresponding sub-categories that respond to the data recorded. It is like a question/answer system, so the initial question field has data entered that requires answers entered and/or available (from previous experience) in the corresponding answer table. I have been thinking that I need to have the 91sub-categories in their own tables to limit the number of fields per layout. Is this a limitation? Is there anything wrong with having loads of fields in one table? Each of the 91 sub-categories will need to be related to the entity asking the question and receiving the answer. The questions and answers will eventually be presented in a report. In the future, when the answers have been acted on, the process is redone using the same sub-categories and the latest report is used to show progress, or otherwise.

I could create tables for each main category and split the one with 34 sub-categories into 3, making 8 tables populated with the fields from the sub-categories which would result in approximately 50 to 100 fields in each table.

 

[Kris M]

http://help.filemaker.com/app/answers/detail/a_id/14164/~/technical-specifications-of-filemaker-pro-14-and-filemaker-pro-14-advanced

Nothing wrong with having "loads" of fields in one table as long as the are not loads of unstored calcs.

An internal red flag should be raised when you have to use field1 field 2. etc.

Thats a sure sign that you need to normalize. 

https://en.wikipedia.org/wiki/Database_normalization

 

 

Edited January 25, 2016 by Kris M

[dwdata]

Hmmm - so am I on track with understanding your needs?

MAIN_CATEGORY table (primary key is _pk_main_category_id)  with ONE TO MANY records (you mention 6 records)

SUB_CATEGORY table (primary key is _pk_sub_category_id / foreign key is _fk_main_category_id)  with ZERO TO MANY records for each MAIN CATEGORY record (you mention most main categories have 9-12 sub-categories)

QUESTION table (primary key is _pk_question_id / foreign key is _fk_sub_category_id)  with ZERO TO MANY records for each SUB CATEGORY record (I guess you would have X amount of questions per sub-category)

ANSWER table (primary key is _pk_answer_id / foreign key is _fk_question_id)  with ZERO TO MANY records for each QUESTION record (then you would have X answers to each question)

Does this sound close or am I missing something? More info please. Thanks! ;-)

[Jo_In_Oz]

Thanks for the links Kris M. There shouldn't be too much in the way of calculations, just relationships linking records together.

 

Thanks dwdata, that does make sense. I am new and don't have a great grasp of how things link up yet, but your solution gives me a broader idea of what I can do. I will try it out! In the meantime, here is a summary of sorts, of where I have travelled so far in my first adventure with FMP.

I have only constructed one table on the question side of things, so far, to experiment with. It represents one sub-catergory (called EN01) of 9 in the main category of ENs, ie EN01 to EN09. There are 5 more main categories eg, EN (with 34 sub-categories), LA, HR etc. Each sub-category has fields that required data entry. Each record of data that answers a question needs to be related to another field representing answers. Q: You could ask how many blue socks at this time? Ans: '100' then relates to a table with possible solutions to how to change the number of blue socks.

So a main category could be clothing, another could be furniture etc with sub-categories being socks and chairs etc. Because there are so many different types of clothing, with colour and material etc possibilities, and each record relating to a person, I am not sure how to set up a solution with so many records and to do it in such a way to be able to report the current situation and then report on how things are at a later date for comparison.

So far, as far as relationships go, I have tried a few different relationship setups. I see many 'persons' to many 'questions', because the questions are asked again later from the same person, so I tried a join table of 'persons questions'.

So I had _pk_person_id related to _fk_person_id in the join table 'person question' and in the sub-category table I had _pk_EN01_id related to  _fk_EN01_id in the join table 'person question'.

I then built a portal related to EN01 in the 'person question' layout hoping that the data I entered into the 'EN01' layout, using a related '_fk_person_id' field to link that record to a person, would show up on the 'person question' layout.

That didn't seem to sync. I now have 2 TOs for 'person question', one related to 'person' and the other to 'question'. It works in displaying data, much like the first setup, but it doesn't automatically show up the data from the EN01 records, I have to create a new record and select the person. The data does show up then.

I go ahead now and get my head around your suggestion. I am sure I will learn a lot on the way.

Thanks again for your help.

 

[Jo_In_Oz]

The term zero to many is new to me. From what I have picked up it is the same as one to many with the option of one being zero, ie an empty field. This is perfect for me as not every person will answer every question. Makes no difference to the set up though, does it?

 

[Jo_In_Oz]

I set up following your instructions. I am not sure where to place the fields that hold the questions. If in the 'questions' table, then I need to have the 'person' foreign key there I guess. My first sub-category (of 91) has 10 questions that require data input into the question fields. If I populate the questions layout with all the questions it will ge very long. Or do I start at the top, at person or category, and work my way down through droplists or similar. The questions need to be stored somewhere. Where do you suggest?

[LaRetta]

9 hours ago, Kris M said:

Nothing wrong with having "loads" of fields in one table as long as the are not loads of unstored calcs.

As an aside, I wouldn't phrase it that way, Kris.  Everything has weight and particularly over WAN, tables should not be wide (large number of fields).  All fields (except container, summary and unstored calculations) get downloaded when you fetch a record.  And all those fields must be uploaded again when you change data in even a single field.  So if you can off-load less-often-used fields to a secondary one-to-one (1:1) table, it can make an obvious speed difference.

With narrower tables, fewer packets get transferred back and forth ... and every packet counts ... particularly over slow connections.  This has only become noticeable since we've gone mobile but it is a reality and we all should design for it.

Edited January 26, 2016 by LaRetta

[LaRetta]

This has only become noticeable since we've gone mobile but it is a reality and we all should design for it ... IF there are fields which aren't accessed as often and if you have hundreds of fields, you can certainly cull the herd.  :-)

Edited January 26, 2016 by LaRetta

[comment]

8 hours ago, Kris M said:

Nothing wrong with having "loads" of fields in one table

In addition to what LaRetta just said:

It's true that in theory a table could have a large number of fields without breaking any normalization rules. In practice, however, most objects will only require 20-30 fields to describe them. In fact, there is a thread here with a bet that no real-life object could legitimately have over 100 attributes that require tracking.

So it is a good rule of thumb to stop for reassessment when you reach the "loads of fields' level. Chances are you are breaking normalization, either by describing children (numbered fields) or by describing the parent (repeated values in a field).

[ggt667]

Through my experience the only table that could sometimes gather some fields is the settings table( 40 - 400 depending on parameters and scenarios, ) but that one hardly ever have more than 1000 records( one pr user, or even less one pr department or location ) My largest logistics soution has about 220 tables, but largest field count is 10 for the table, 15 actual as I have some fields in each table for admining purposes such as timestamps, recid, modid, [CRU]Ux( Mark for deletion by user, ) and a couple of others.

 

I believe if you would like to see data normalization in practice it could be a wise choice to download an sqlite browser and take a look at the data from your Addressbook in the Mac OS X.

Edited January 26, 2016 by ggt667

[comment]

@Jo_In_Oz

I couldn't understand most of your description. In general, if you have people answering questions, you should have:

People -< Answers >- Questions

If the questions are categorized and sub-categorized, then your core relationships would be:

People -< Answers >- Questions >- SubCategories >- Categories

and you may need a few auxiliary relationships, e.g. to select a question by selecting a category and a subcategory first.

None of this would lead to "hundreds of fields", in any table.

 

Edited January 26, 2016 by comment

[ggt667]

One thing that is not mentioned yet is that in FileMaker or almost any other RDBMS, you will be able to use the same table in several locations in the graph, for example my contact table is only 1,

but that table is being used in the graph for: user, PIC, office, buyer, warehouse, deliveryLocation, pickupLocation, invoiceAddress, vendorAddress, and 5-10 more places depending on the need.

Edited January 26, 2016 by ggt667

[Jo_In_Oz]

Thanks for all your help with the above. Perhaps another way of describing my situation is as follows:

If a database had a services table with six different categories of services, and each category had variations, from 9 up to 34, totalling 91 categories, and each of those categories had a number of fields requiring data input, both qualitative for example pricing and qualitative for example quality of service, how would this be set up in a table so the user can choose particular services to enter data on? I keep coming back to the need to create tables for each of the 91 categories.

And then if customers used the same services over time, how would the relationship be shown in that many to many relationship? ie many customers using many services.

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? Would a join table with both product and customer foreign keys produce the records produced in the products table? If so, how to access that information? Perhaps by putting in fields from the parents?

I am hoping I am on the right track. Which table to use to input data is confusing me currently. Maybe a portal in the join table if one is required?

[comment]

4 minutes ago, Jo_In_Oz said:

Which table to use to input data is confusing me currently.

Well, I am kinda confused by what you describe. This is because you are concentrating on tables, instead of describing the real objects that your database is supposed to track.

If your customers subscribe to services, than - assuming a customer can subscribe to more than one service, and vice versa - you would have:

Customers -< Subscriptions >- Services

9 minutes ago, Jo_In_Oz said:

Most importantly, where would the data be entered?

The data describing a customer (name, gender, age, address, etc.) would be entered into the Customers table. The data describing a service would be entered into the Services table. The Subscriptions table - which serves as a join table between the other two - would hold the CustomerID, the ServiceID and any information describing the specific join of the two, for example the date of the subscription.

As for the categories, I am not sure what exactly they represent and what data they need to hold.

[Jo_In_Oz]

Hi comment, I didn't get into detail as it gets a bit confusing so I tried to give an example. 

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. I have 6 main assessment categories arising from economic, environment and social issues. 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 are categories based on social indicators with sub-categories ranging from 9-12. The first sub-category in EC (the main category) is EC01 (the sub-category), and it has 10 indicators requiring data entry. Each sub-category has a number of 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 of the main categories have 9-12 sub-categories (except EN, which has 34), and each sub-category has several items requiring data entry (eg EN01 has 10). This leads to 91 sub-categories, all with there own list of indicators that require data entry, leading to hundreds of fields requiring data entry. This may be difficult to work with if I cant find a way to split them up while maintaining relationships with the client.

Should I make tables for each sub-category, so for example EC01 with its 10 fields would be in one table? I thought a join table called 'client_indicators' between clients and indicators would work, but then EC01 and all the others would need to relate to 'client_indicators'.

I hope this makes some sort of sense.

[comment]

2 hours ago, Jo_In_Oz said:

I didn't get into detail

Well, the devil is in the details - in this case, the missing details.

 

2 hours ago, Jo_In_Oz said:

Each sub-category has a number of indicators requiring data entry.

What exactly is an "indicator"? What does it look like? Could we see an example or two of an indicator - before the "data entry" part, and after?

 

2 hours ago, Jo_In_Oz said:

Every record needs to be related to a client, who will want a report on their current level of sustainability

I presume this means a client will enter the data (as discussed above) in response to some or all indicators? What determines which indicators will each client be requested to report on?

Also, will a client submit more than one report over time? I guess that would be yes, since you mention "future reports".

 

2 hours ago, Jo_In_Oz said:

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 part is not clear at all. Again, you speak of tables, fields and relationship, when you should describe the real-life objects. Could we have an example of what an assessment looks like? Will it have a structure that shadows the report submitted by the client (e.g. the client submitted a report providing data on 850 indicators in 90 sub-categories. Will your assessment have 850 parts? 90 parts? Or?).

["... you mean these fans?"]

Hi Jo_In_Oz,

I see two tables, possibly three if you want to list your sub categories based off of the category selected.

I would need to see these indicator / questions, but off the cuff I would assign a numeric value ?? based on the data entered if possible ... again I would need to see these indicators.  I'm thinking 21 radial buttons -10 ... 0 ... +10.  If you can keep your data all in one table, you will be able to build your reports via "Filemaker's" SubSummary sorted by ... "Field" and then just "Add" up your indicators.  You could sort your report by "Date; Quarter, Semi-Annual, Annual" etc. Again, I would need to see what these indicators / questions are.

1st table is your contacts / clients table.

2nd table is your table that will carry the data associated to your Customer/Clients.

3rd ( optional ) table is your "Category / Sub Category" table list.

Your contacts table will carry the "pk_Contacts_ID" for each contact.

Your "Data" table will carry two "ID" fields ...

pk_data_ID

fk_contact_ID

So let assume you've created all your contact for the most parts and are now ready to assign the data you want to enter ...

On your "Data_Table" create a record, this will auto create your pk_data_ID.

Now that you have this record created, you will assign information to three key fields.

Field One ... fk_Contact_ID will be assigned a contact ID which you can use a value list looking at your "Contacts" table to select which contact to assign.

Field Two ... you will assign the main "Category" to field "Category"; you can build a value list for this assignment or use the optional category table in your value list.

Field Three ... you will assign the a "Sub Category" value to field "Sub_Category"; you can build a value list for this assignment or use the optional category table and based off of the "Category" selected you can list the SubCategory values related to category "EO1" for example.

Now once you've established these three key fields, you now have the ability to look at your data table based off of each contact, their categories and / or subcategories.

Instead of using many category "Tables" which you will have a very difficult time building reports via a signal piece of paper ( This is what customer want ... one report on one piece of paper in a sense ... with no restriction in length ) you'll need to keep your data in one table, so I would navigate the user to different "Category Layouts" within the same data table.  This way you can organize and keep your category data and subcategory data accordingly ... and then maybe provide a layout on the same data table that would present all your categories and sub categories with summary totals ... but be careful here.  If you are hosting this solution, summary data has a tendency to slow you database if calculations live on some other table.  

On the "Category Layouts" I would build "Portals" related to the "Contact ID" that would list all the "Category" reports, that way all you need to do is select that report in the portal to look at it.

I'm cautious here since I am not really understanding you "Indicators / Questions".

Tom :-)

 

Edited January 26, 2016 by Snozzles

[Jo_In_Oz]

Thank you so much for your time and effort in replies.

I will attach a few images that will hopefully clarify what I am working with.

I am the user at the moment. I will go to businesses and assess their levels of sustainability based on the widely accepted GRI (Global Reporting Initiative) documents.

To do this I have extracted the dry data requirements held within the GRI and I am also developing questions of a more practical nature to apply during visits to businesses (with my iPad), rather than relying purely on GRI driven data. My aim is to develop a system that can report using the GRI structure, while making practical assessments resulting in practical advice for clients. So the reporting is 2 fold, one based on the GRI to gear towards inevitable compliance and the second to provide clear information for clients as to where they are now in terms of sustainable practices, how to improve, and then later show reports on their improvements environmentally, economically and socially. 

The 6 main categories, with the subsequent 91 sub-categories come from the GRI.

The relationships window image gives an overall example of the ideas I have been working with.

The FMP_EC01 image is an example of one of the 91 sub-categories with its indicators requiring data entry.

The sub_categories image shows an incomplete list of the 91 sub-categories to give an idea.

The FMP_Cl image is from a stage where I had clients>clients_indicators<EC01 indicators related with a portal linked to EC01 indicators. I couldn't get clients_indicators to automatically provide a record and it also was related to one table, being EC01 indicators (in this case from the GRI). 

Snozzles, I think I get what you mean. I will study your post and get my head around it.

In the meantime, I hope the above details help provide a better basis for your much appreciated advice.

Thanks again,

Jo

FMP_CI.tiff

FMP_EC01.tiff

Relationships window.tiff

sub_categories.tiff

[Jo_In_Oz]

I have included EN01 Indicator questions from the sub-categories related to the main category of EN, being for environmental indicators, to show the kind of questions asked. These questions could have Yes/No buttons.

The relationship image shows the the graph when I made the FMP_Cl layout in the previous post. This is where I was trying out a child table to both clients and indicators with layout including a portal from indicators. It worked ok but I couldn't get it to automatically generate the records per client.

What I am hoping to achieve is that I can use one layout that is child related to both client and sub-categories, with a portal related to sub-categories that has a dropdown/popup list with value lists attached, then select the appropriate sub-category, which will display the associated GRI indicators and the practical questions for data entry. Once I enter the data for that sub-category, I can then select the next sub-category and enter data into its GRI indicator and question fields/records and so on. All the time this data will be related to one client and the record of all this data has been automatically generated with its own id.

I think that is what Snozzles is suggesting, which gives me some hope that I may be on the right track.

 

EN01 Indicator questions.tiff

relationships.tiff

[comment]

Based on what I understood so far, I believe you need to have a structure similar to this:

Please note:

The Indicators table contains all indicators, in all categories and sub-categories - and every indicator (question) is a separate record. In other words: if you have 91 sub-categories, with say 10 indicators on average per category, then there will be 910 records in the Indicators table;

Likewise, there will be 91 records in the Subcategories table - not 91 fields as shown in your graph;

This is an ERD: your relationships graph will end up looking more complex than this;

Here we deal only with your data model. You have an additional issue of how to build the user interface so that you can (for example) view all questions in sub-category X and enter the current report's responses for them. However, this complexity should not have any influence on how your data is structured.

 

 

[comment]

15 minutes ago, comment said:

You have an additional issue of how to build the user interface so that you can (for example) view all questions in sub-category X and enter the current report's responses for them.

To give you an idea how to create/display responses against a grid of questions, I suggest you look at http://fmforums.com/topic/98488-show-a-portal-of-potential-entries-not-yet-created-based-on-dates/#comment-447756 and http://fmforums.com/topic/98863-personnel-training-and-completion-dates/#comment-449625

 

[Jo_In_Oz]

Thank you! As I have continued to work on this I have come to a similar diagram.

I have been experimenting with 6 tabs for the main categories/subjects on the client_indicator/response layout. Each tab has, for experimental purposes, 9 slides with portals related to the sub-categories with the respective indicator fields showing. 

What do you think?

[comment]

5 hours ago, Jo_In_Oz said:

What do you think?

I think you only need one portal. You may find it convenient to use buttons (fake tabs) or even a real tab control object to control what the portal shows, by filtering the portal or the underlying relationship. But by placing multiple portals inside multiple panels you would sentence yourself to having to replicate any design change 54 times.

[Kris M]

On 1/26/2016 at 3:14 AM, LaRetta said:

As an aside, I wouldn't phrase it that way, Kris.  Everything has weight and particularly over WAN, tables should not be wide (large number of fields).  All fields (except container, summary and unstored calculations) get downloaded when you fetch a record.  And all those fields must be uploaded again when you change data in even a single field.  So if you can off-load less-often-used fields to a secondary one-to-one (1:1) table, it can make an obvious speed difference.

With narrower tables, fewer packets get transferred back and forth ... and every packet counts ... particularly over slow connections.  This has only become noticeable since we've gone mobile but it is a reality and we all should design for it.

If the OP had said anything about WAN deployment my reply would have been different.

Would i suggest a table with 400 fields? no.

Can it be done? yes.

On 1/26/2016 at 3:54 AM, comment said:

In addition to what LaRetta just said:

It's true that in theory a table could have a large number of fields without breaking any normalization rules. In practice, however, most objects will only require 20-30 fields to describe them. In fact, there is a thread here with a bet that no real-life object could legitimately have over 100 attributes that require tracking.

So it is a good rule of thumb to stop for reassessment when you reach the "loads of fields' level. Chances are you are breaking normalization, either by describing children (numbered fields) or by describing the parent (repeated values in a field).

I agree that a table with hundreds of fields is less than optimal.

I provided an answer that addressed the first line of his original post

[comment]

9 minutes ago, Kris M said:

I agree that a table with hundreds of fields is less than optimal.

That's not exactly what I said. My point was that a table with hundreds of fields is suspect. Those fields could be legitimately required - except that in most cases they represent a violation of normalization rules.

I have no quarrel with what you said. I just thought it needs expanding, because numbered fields are not the only thing that should raise a red flag. We have a good example of that right here in this thread:

 

[webko]

I had a survey system that supposedly required a structure like the one illustrated above - a Section-Sub-Section-Question for fields, with an answer for the data

So I made the actual database for holding the answers have fields for Section, Sub-Section and Answer (the Questions could be drawn from a master table based on Section and Sub-Section)

I also had some foreign keys for the Company and Person filling out that particular survey.

So, about 6 or 7 fields rather than the 650 that would otherwise have been required.

[LaRetta]

On 1/25/2016 at 3:45 PM, Kris M said:

Nothing wrong with having "loads" of fields in one table as long as the are not loads of unstored calcs.

Not meaning to quibble but 1) I felt your sentence should indicate 'unless used only locally or LAN) so that is why I added that caution.  

On 1/26/2016 at 0:51 AM, LaRetta said:

This has only become noticeable since we've gone mobile but it is a reality and we all should design for it

So no, Kris, there is nothing wrong with what you said but I stand by the fact that today, most solutions are expected to be accessible more than just locally or LAN.  In fact, desktops are decreasing at rapid rate and I spend a lot of time redesigning folks to speed them up.  Narrow tables are an important aspect of those redesigns.

[Jo_In_Oz]

18 hours ago, comment said:

Based on what I understood so far, I believe you need to have a structure similar to this:

Please note:

The Indicators table contains all indicators, in all categories and sub-categories - and every indicator (question) is a separate record. In other words: if you have 91 sub-categories, with say 10 indicators on average per category, then there will be 910 records in the Indicators table;

Likewise, there will be 91 records in the Subcategories table - not 91 fields as shown in your graph;

This is an ERD: your relationships graph will end up looking more complex than this;

Here we deal only with your data model. You have an additional issue of how to build the user interface so that you can (for example) view all questions in sub-category X and enter the current report's responses for them. However, this complexity should not have any influence on how your data is structured.

 

 

I have a table called GRI codes that has all the 91 records of the 91 sub-categories. I use the Global Reporting Initiative (GRI) codes to standardise reporting to a globally accepted practice. If I put all the Indicators in one table (yes, indeed hundreds of them), I will need to identify which ones are associated with respective GRI codes (the 91). Is there any danger in adding the GRI prefix, for example EN01, to field names related to the sub-categories eg EN01? I note in a previous comment you mentioned this was not advisable.

My lack of experience with FMP presents a road block in that I do not know how to link the GRI codes table with its 91 records to the Indicators table. I have the relationships in place but I don't know how to display the required fields for entry. I have experimented with setting up the tables as suggested, although for now I have kept Indicators represented by the 2 tables EN01 GRI indicators and Indicator questions. I can bring all together into the Indicators table once I am sure that is the best option. Do I need to run scripts or adjust field behaviours? I have tried a few but to no avail, yet. There are issues to do with page layouts for data entry as mentioned, but the architecture is the first issue, then onto building. I have added a few images to provide some context to the kind of data requiring entry.

The Responses table, (which I have called Client_Indicators), is a join table between Indicators and Reports. Reports will provide a sustainability assessment for the client, and will be repeated to provide comparisons to previous assessments.

I considered an Actions table associated with Response, to provide an action plan for the client to improve sustainability, based on the data in Responses. The fields in Actions are the same as Responses, so this leads me to believe I can extend the Responses table to provide options for each indicator. It is my intention to grow the list of possible actions for each indicator as my experience as a sustainability consultant grows. I would need to relate the appropriate actions back to the client in a report called Action Plan.

The Relationships image shows the current setup following the suggested ERD, although with 2 EN01 tables until I modify that aspect.

GRI EC01 shows a layout that indicator. Is it possible make a layout that would display those particular indicators each time, related to a client?

EN01 Indicator questions shows a range of questions associated with EN01 that are used to assess sustainability.

EN01 indicators shows the GRI indicators for EN01. This one is tricky as it has multiple entries for easc field, roughly displayed in Raw materials used. Each entry must also be qualified with External or Internally purchased or sourced and if a renewable material or not. The number of entries required will vary with each client.

It's a complex undertaking for a newbie, but I do tend to dive into deep ends and then swim like hell. 

Thanks for you help so far,

Jo

 

 

GRI EC01 indicators layout.tiff

EN01 Indicator questions.tiff

EN01 indicators.tiff

Relationships.tiff

[comment]

I am afraid we're still not talking about the same thing. If you ever intend to summarize your reports, you must have a separate record for each answer. And, accordingly, you must also have a separate record for each question. Your RG screenshot shows a field per question - and what seems to be an attempt to create a separate table for each category.

Before pushing you further on this path, I should also warn you that this is not going to be simple, esp. if your questions do not follow a uniform format (e.g. some require a numerical answer, some a choice from a value list, and some expect text). If you do not intend to summarize the answers, then making just one huge questionnaire, where every question is a field, would be much easier. In such case, you would only need a table of Reports (with approximately 1,000 fields) related to Clients. But then the only thing you would be able to do with the collected data would be to print it (in some arrangement or other).

--
P.S. If you're attaching images, please make them PNG or GIF or JPG.

 

Edited January 28, 2016 by comment

[Jo_In_Oz]

I will want to summarise reports, so separate records for each answer is necessary. The seemingly attempt to create seperate tables for categories was more of an attempt to display the kinds of questions/data I am dealing with, and in the case of the indicators, not committing all indicators to one table until I am confident of how to work with and display the data.

Some fields may need to be repeated as there are a number of entries required such as Materials used, which will usually have a number of different types and amounts (weights) of materials used. 

8 hours ago, comment said:

field per question

and

8 hours ago, comment said:

separate record for each question

Is it possible to have one field with a number of records that remain in the one overall record? Related to the one client?

Yes, it will be complicated. Although without the structure of FMP to work within, it will be far more complicated to keep the data together and reporting for assessments and actions.

Imagine trying to do all this with excel, as many do. It will be worth it in the long run, maybe even create an app! Yes, ambitious I know.

Just confirming I am committed to an outcome as hard work now will improve productivity and accuracy later.

Thanks again

 

[Kris M]

On 1/27/2016 at 4:08 PM, comment said:

I have no quarrel with what you said. I just thought it needs expanding, because numbered fields are not the only thing that should raise a red flag. We have a good example of that right here in this thread:

understood and i concur that its highly suspect. Quarrel is always optional

[Jo_In_Oz]

Perhaps the issue is, due to my inexperience, I have been lost on how to create the Sections, sub-sections and answer you describe, drawing questions from the master table .

On 1/27/2016 at 7:19 AM, webko said:

I had a survey system that supposedly required a structure like the one illustrated above - a Section-Sub-Section-Question for fields, with an answer for the data

So I made the actual database for holding the answers have fields for Section, Sub-Section and Answer (the Questions could be drawn from a master table based on Section and Sub-Section)

In my case there are 6 Sections, 91 sub-sections, approximately 1000 individual questions (not repeated anywhere). I think I am missing the point on how to the relate the records from sections to sub-sections. I think I have been thinking fields, rather than records, perhaps. 

I still get stuck thinking that each Indicator question needs to be hardwired to a field name in a table, rather than a record in the parent table. Once I see how the relationships, tables and layouts look, I will be closer to that AHAA! moment. Am I closer? Anyone??

If so, I think I get this now:

On 1/25/2016 at 9:47 AM, dwdata said:

MAIN_CATEGORY table (primary key is _pk_main_category_id)  with ONE TO MANY records (you mention 6 records)

SUB_CATEGORY table (primary key is _pk_sub_category_id / foreign key is _fk_main_category_id)  with ZERO TO MANY records for each MAIN CATEGORY record (you mention most main categories have 9-12 sub-categories)

QUESTION table (primary key is _pk_question_id / foreign key is _fk_sub_category_id)  with ZERO TO MANY records for each SUB CATEGORY record (I guess you would have X amount of questions per sub-category)

ANSWER table (primary key is _pk_answer_id / foreign key is _fk_question_id)  with ZERO TO MANY records for each QUESTION record (then you would have X answers to each question

I have a better understanding of the layouts advise now too: 

On 1/26/2016 at 7:09 AM, Snozzles said:

different "Category Layouts" within the same data table.  This way you can organize and keep your category data and subcategory data accordingly ... and then maybe provide a layout on the same data table that would present all your categories and sub categories with summary totals

and 

On 1/26/2016 at 7:09 AM, Snozzles said:

On the "Category Layouts" I would build "Portals" related to the "Contact ID" that would list all the "Category" reports, that way all you need to do is select that report in the portal to look at it.

Makes a bit more sense now as I begin to understand the flexibility of layouts and portals.

Cheers,

Jo