Help, how to handle unknown number of columns?

How would I handle this scenario:

Goal:
Want to expose this through a form.

Customer A
This customer has several projects. With each new project I need to create new columns, because the information usually is different from project to project, so it’s very possible that I will not be able reuse a column later on.

Example:
Project #1, one Multiple Select is
“A”
“B”
“C”

then, later on, I create a new project #2, and it needs a different Multiple Select:
“1”
“2”
“3”

then, later on, I create project #3, and it needs two Multiple Selects:
Multiple Select #1:
“Answer one”
“Answer two”
“Answer three”
“Answer four”
“Answer five”

Multiple Select #2:
“Some text”
“Other text”
“Another text”

Question:

What is a best practice to create a database (and its tables) able to hold this information?

Should I create a “Customer A” table, and hold every project of that customer in there?
or would it be preferable to create a “Multiple select” table, and hold every type of multiple select question in there.

Also, this is just for one customer, Customer A.
Any guidance on how would I go and accomplish this for many customers?

Or can I use the “Multiple Select” column to keep any and all records?

So, my question is:
When using a form, can I select which fields I want to display, for that particular form, of a “multiple select” table?

2 Likes

Started reading on databases, this looks like a good approach
a7

As I understand it,

  1. I would have to create a “Questions” table,
  2. create any question in there, and then
  3. reference it?

Hey there :slight_smile:

This is a question of database normalisation, I will actually write a blogpost about that soon since this is something that users have struggled with in the past.

So generally speaking when you design a database you want to avoid having null (empty) values in a row. So adding every single possible column to a table and then just leaving the entries blank for the projects that don’t need those columns would be considered bad practise.

  1. I would have to create a “Questions” table,
  2. create any question in there, and then
  3. reference it?

This is sort of correct, but it is not possible in Baserow. The best practise this is describing is, that you should never have multiple data points in one cell, and if you do run into a scenario where you do have to have multiple values you should create a separate table and reference the data.
There is a discussion about exactly that right here: Multiple data in one cell : Is this possible? A comma between each gives me only one data/entry (#1253) · Issues · Baserow / baserow · GitLab

What you are suggesting (or at least that’s what I understood) is that you would want to create a table with questions and then reference those questions as options in the multiple select field.

That would be a very good architectural solution, if you could define the options of the select field for each row individually using a reference to the questions table, but that is not possible in Baserow.

So after all that being said, what is the best solution? Unfortunately as it stands right now, I think you will have to create a separate table for every project, that would be the cleanest solution that would not violate any best practise in my opinion.

If anyone has a better idea please let me know, there is always a chance I haven’t thought about some clever idea that might make this work after all :slight_smile:

1 Like

Yes that’ the idea

Another drawback I notice is that it could soon get messy, having every single question stored in one table. I’d need a huge number of columns, where what I’d really need are rows and a small number of columns, to keep it clean and manageable.

Thanks.