I’ve got a question about how to correctly organize my data for good long-term maintenance.
The project is simple:
There is a public form to declare an interaction with a politician.
There is a private dashboard to view these interactions and easily access data about the concerned politician.
I succeeded in creating a cell in the Interaction database from the Politicians database to provide the user with a single search input in the form to select a politician. It works well.
The problem comes from my Politicians database. Currently, I have six different CSV sources to create this table. The columns are not exactly the same; they depend on the politician’s function.
Additionally, this list can be updated over time because politicians change regularly.
So, I’m questioning whether I should create six different tables. But that would result in six different inputs in the form, which is not user-friendly.
Should I consolidate everything into the same table? But that would leave me with many empty columns, and it will be a mess to update the list over time.
It’s pretty hard for me to give you a piece of advice as I don’t have a global understanding of your problem, especially:
what column(s) of each csv do you actually need to populate your Politicians table?
is the structure of these different csv something that could eventually change over the time?
etc.
However, long-term maintenance is always complex as you don’t necessarily have the answer to those questions (the second one for example). That’s why it’s always better, in my opinion, to do something modular, but be aware that the most modular is not necessarily the simplest or easiest to understand/work with!
In such a use case (as far as I understand it), I’ll definitely stick to one single linked column to choose the politician, which means consolidating every politicians from the six csv files in a single table.
To do so, you first have to answer the question question what data do I need to store about each politician?
Once I know the answer, I’ll create a (Python) script to feed the different columns of my Politicians table from the different csv files (one import functions per file structure so possibly six different import functions), which sounds relatively modular to me in anticipation of possible modifications to the structure of these files.
As you will run these import functions several times as the lists will update, you will have to ensure that you don’t create duplicate records, and you’ll have to choose what to do when a record in a list is already present in your table (which data do you keep ? Those on the list or those in your current database?).
Hope this helps…
Bests,
Benjamin
I love spending time gathering information and explaining solutions to help you solve your problems. I spend… quite a bit of time on it .If you feel grateful for the help I’ve given you, please don’t hesitate to support me. I am also available for paid services (scripts, database architecture, custom development, etc.).
Yeh, I think that the structure can be upgrade, with more columns, but there are not lots of reasons to decrease it.
I’ll try to do a single table I think, it looks like to be the best to have all I wanted.
I’m thinking about using Make or Zappier to build an automation to make the update in the future, I’ll take a look about this.
@bennhatton I think that is a valid solution, and one I would definitively consider myself.
However, I’d like to propose another thought that puts the consolidation within Seatable instead of python. Just a hunch, but in the end, I guess I will be asking the same question: How will you update the source data in Seatable on a regular basis a) without creating duplicates (=sync), and in addition to b) make sure that the updates do not break the links to the interaction table.
Actually import all 6 different politician files into 6 different tables.
Create another table like “Politician Master” which you will link to your interactions later.
From that table, create 6 different link columns to the separate source tables.
Now you have to link each source record from each source table to a separate line in the “Master” table
There’s a very intelligent “auto link” copy & paste function in Seatable that makes this just 6 simple copy and pastes.
However, I’ll not explain it here, because while I’m writing this, the feeling grows that this way leads to nothing.
Anyway: In the “Master” table, you would use formula columns to draw the data from the linked source tables
Something like if(isempy('LinkToSourceOne'),{LinkToSourceTwo.Name},{LinkToSourceOne.NameColumn})
As you can see, that also takes care of the fact that the name is held in differently named columns in Source One and Two
The expression would be nested 6 times, and therefore quite long.
There needs to be a formula column for every other data drawn from the source tables
Having written this down, I prefer the python (or, in my case, n8n) solution of @bennhatton to consolidate the sources outside seatable. The solution I subscribed has some problems:
Updates of existing data needs to be synchronized manually, without duplicates.
Additions to the sources need to be manually linked to the master table.
Do it like thousands of other people who have used SeaTable to develop powerful processes and get their ideas and tasks done more efficiently.
.If you feel grateful for the help I’ve given you, please don’t hesitate to 