Database modeling - using unique identifier to auto populate second table

Baserow Help
1

I need help with the following database modeling scenario:
I am working on dataset of vehicles and their owners, where Vehicle VIN number is the primary field and unique identifier for each row of records downloaded into table “Cars” (like in the first pic). Is there a way to create second table called “Owners” where all record rows I downloaded in to “Cars” table would appear automatically with the primary field “Owner” (like in the second pic that I created for illustration purposes using “linking” and “lookup” fields).

Cars
Cars1000×551 126 KB

Car owner table
Car owner table1242×702 197 KB

I understand for visual filtering I can create a View and Group by “Owner” option, but it does not allow me to make “Owner” field as primary (so I can link other tables to it) and also I cannot link other tables to views in general. If those two options would be possible it would be an ideal solution for my scenario.

While each vehicle VIN is unique identifier for each row in the downloaded dataset, some owners own multiple vehicles and owners are primary point of contact for marketing, call center and record update purposes, therefore “Owner centered” table is needed. Any suggestions very appreciated. Thanks.

1 Like
2

Hi,

The first thing you need is a list with the names of the owners where each name only appears once. You can achieve this be sorting the table cars based on the owner, export to view and manually delete duplicates. Or you can export the view an let a JS or python function do this for you.

Once you have the CSV file with the unique owners, take the following steps

  1. Create a new table Owners
  2. Import the CSV file with the unique owners, but only import the field with the name of the owner.
  3. Create a new table Cars (this will overwrite your original table)
  4. Set the structure for the table with the fields VIN, Make and model as text fiels and Owner as a field that links to the Owner table.
  5. Now import the original CSV file that you have exported and it should automatically recognize the owner as a linked field.
3

Thank you @frederikdc as always for your advice.
Wanted to ask if you can suggest any method to automate above scenario with possibly additional plug-ins, add-ons or “works with Baserow” apps. Ideally it would be when uploading car database with VIN as a unique ID, it would populate “Owner” table (Owner name as unique ID) with cars/VIN’s per each owner. I just need pointer in the right direction. Thanks again.

4

Hi,

You can use an automation tool like Make or N8N to implement such a scenario. If you have some basic notices of JavaScript, I would recommend N8N because it has a Code node. This allows you to easily filter certain values and prepare your input for further nodes.

5

Thank you very much for the advice.