Foreign keys and many-to-many relationships: In playing around with some of the demos it seems like the “link to table field” is not actually a foreign key as is usual in a database but simply keys stored as a JSON object. Is this correct? If so, how is this actually scaleable? Also, how do you model many-to-many relationships in Baserow? If the foreign keys are simply JSON objects then I guess many-to-many doesn’t really exist (you just create a link to table on each table), but wouldn’t it still be better to create a kind of JOIN table in baserow to link two tables and then create the “link to table” fields in the JOIN table?
UUID: Is there a function to create a UUID field. This is built into postgres, but I can’t see to find the function anywhere or a field like this.
Primary Keys and Enforcing Uniqueness: In your documents you say that the first field in any table is a Name field which is a primary field. But, in looking at the templates, you can easily duplicate any name in the table, and so how is this a primary field, if the values don’t have to be unique? So what is the actually primary field in every table? Is the actually primary key just a classic autoincrement ID integer value which is the row number? If so, what’s the point of the primary field? And how do you enforce uniqueness of this field or any field for that matter? Enforcing unique values is a key feature of postgres, to prevent duplicate values.
Foreign keys and many-to-many relationships:
I am not sure what exactly you mean by “keys stored as a JSON object”, but in the frontend the underlying data is indeed a JSON object which has the id and name of the related row.
When it comes to the backend, in order to make this relationship possible, we have a many to many table for every link row field that you create which can link primary keys of one table to primary keys of another table, so I think that is what you would want to see for scalability or am I misunderstanding your point?
UUID:
We have indeed not implemented that field type. There are many field types that people like to see that we haven’t come around to implementing just yet if you think this is important for your project then please feel free to create a feature request either on this forum or on our gitlab page and we will make sure to discuss it internally
Primary Keys and Enforcing Uniqueness :
Yes, it is correct that the “primary field” does not have a uniqueness constraint and yes it is also correct that every row has a “standard” row id as their real primary key. The name “primary field” might be misleading if you look at it from a technical database perspective. What a primary field in Baserow is, is mostly a “special value” that can be used for all sorts of things. For example, if you link to a table, the value shown in that row is going to be the primary field value of the row you are linking to. Stuff like that In regards to a unique constraint, we have had a public discussion about general constraints on fields which might be interesting for you: Field value constraints (#647) · Issues · Bram Wiepjes / baserow · GitLab
Does that answer all of your questions? Feel free to reach out if you have any more questions or feedback, we really appreciate the participation in the community forum!
When it comes to the backend, in order to make this relationship possible, we have a many to many table for every link row field that you create which can link primary keys of one table to primary keys of another table,
Great! That was exactly what I was looking for. I was asking b/c there is a trend of some companies storing foreign keys as a JSON object in the postgres database, and while this is a quick way to create many-to-many, it is not scaleable. So just to clarify, behind the scenes you are creating a join table for many to many?
UUID. I think this is a critical field. For most applications you need a UUID on the frontend, as you don’t want to expose the actual id for various reasons.
Enforcing uniqueness: Thanks I took a quick look at the public discussion, and I’ll second the comment by [Mark Stellingwerff } that that this is an absolutely essential feature for any database and it’s strange that most of the No-Code databases I have used don’t have any uniqueness feature. As you are running on Postgres, this should be extremely trivial to implement for a single field (I can see multiple fields being a bit more complex). You just provide option when creating a field to ask if it should be unique and then generate the proper SQL on the backend. Seems extremely easy, but maybe I’m not too familiar with how your project is structured. Anyway, without some concept of uniqueness the applications for baserow will be extremely limited, in my opinion, and users will eventually have very corrupt data.
Once you have decided to run on Postgres, which was an amazing decision, why not use all it’s basic features?
Hey Sorry for the late response, I was on holidays for 2 weeks! I am back now
Correct.
When you say UUID are you referring to a random generated ID that increments itself for example or are you referring to an actual “User ID”. Because if it’s the second, we have just released the collaborator field which allows you to assign a user a column of your table.
@olgatrykush maybe something we want to pick up in a discussion again during our weekly?
@olgatrykush …and if I imported a few tables from CSV and they already have foreign keys / primary keys, how do I set theese up as link in Baserow? If I change the foreign key field (like movie_id pointing to a movie.id field) to “link to table” type, then all the actual foreign keys (movie ids) are wiped out and not turned into links. Also, there seems to be no distinction between one-to-many and many-to-many fields - which is sort of logical as you create a many-to-many link table.
Hey @andrisi, if you’re importing a CSV file, then you probably want to the identification value as primary field. There can only be one primary field in Baserow, and it’s the first field on the left of the grid view, the one that sticks when you horizontally scroll.
If you then create another table, I recommend to first create the fields, and the use the “Import file” feature to fill it with your CSV data. If you have a “Link to table” field pointing to another table, and you use the “Import file” feature, it matches the cell value with the primary field cell value of the related table. That should allow you to import your data.
Thanks @bram it helps. But it also means, that there is no way to create relationships with preexisting data? I tried the other way around, importing data, and then changing the foreign key field to be a link - but that does not work. So it only works, if I create the linking field and primary keys, and then import the data, right?
