I have a main base with around 10,000 records and growing. Each record has as of now 78 fields.
Think of a list of people with a series of answers to questions.
I also have a series of bases (one for each year for three years) and within those bases are survey responses, with one tab for each survey.
Now, my issue is that the surveys ask questions that are only sent to people on the list of 10,000 records in the main base. The surveys all contain answers to different questions that aren’t in the main base.
If I went through and just added every single new answer to the right record in the main base, there would be hundreds of fields in totally. Maybe 300-400. This seems like it could be really unwieldy.
But at the same time I’d ultimately like this data to be connected to an entry in the main base in such a way that I could easily search through these answers (tied to the person back in the main base).
Right now there isn’t any link between an entry in the main base and a corresponding survey response, so any sifting would be a manual lookup.
Just curious if anyone has any ideas about best design principles here. How many fields is too many? Should I just have the one base with a whole bunch of fields? Is there something else I should do?