I’d like some help organizing a relational database that i currently have on airbase (but it could live on any relational database). At the moment im just trying to find a solution for one entry (row). a link is below to the spreadsheet along with an explanation of the problem i hope to solve.
The goal of the database is to estimate the potential impact of research through a product.
For instance, In the airtable below, we have a list of every research article on disinformation and fake news, then another table with hypothetical products based on the research.
In the one row id like help with, there’s a hypothetical product that will tag fake news articles on facebook. This links to a few research articles estimating the potential impact. We have articles that measure that amount of all fake news that is driven by facebook, the effect of viewing a tag alerting users to fake news and the percent of fake news that algorithms can currently identify.
So, in the ideal situation, each one of these effect sizes is multiplied by one another. Roughly speaking, 20% of all fake news is generated on facebook, algorithms can correctly tag 80% and seeing a tag reduces fake news consumption by 10%, then a tagging feature would reduce consumption of all fake news by 1.6%.
The question is, how to organize the columns, given that there are so many possible variables for each article. Each platform, effect and population is a separate variable…this quickly gets quite large.
In the airtable below, ive done a very redimentary version. The key table is “product”. It links to a separate table that is “research” and the research table has a few effect sizes, like one article on fake news has a column for how tagging reduced fake news consumption. that’s one columns right now under “effect size” and another column that specifies the effect size is for tagging. This research links to a table called “strategy”, where “tagging news” has an estimate for impact, then the “product table” which links to the strategy for tagging and combines it with the estimate of fake news on facebook, multiples the two and gives the potential impact.
In the product table, “tagging fake news” it is own column. But, if this keeps up, each row would have thousands of columns for every possible strategy and research citation.
There has to be a better way to organize it. I hope this was somewhat clear and feel free to let me know how i can make the question easier to answer. thank you
