Hi all, I have a question about operating within best practices (My question is not as long-winded as it seems).
I know that Xano supports array (list) structures for each of the field types available in the database. So for the cells of a particular column I can have an array of items in them, like a list of images in the image field of a record/object and that this ability comes from Xano being built on PostgreSQL.
I’ve delved into learning about database design and of course eventually came across database normalisation, in which, the first normal form states that at every row and column intersection in the table (so cell) there exists a single value and never a list of values, each cell can hold only one value. Which of course conflicts with the use of array structures in a table.
But I have also come across the technique of denormalization, where you ignore / don’t apply some layers of normalisation for reasons such as performance, necessary convenience and end-user need. So I understand that you don’t have to fully conform to the rules of normalisation.
100% conformity to normalisation is not practical and so yeah, there is some breaking of the rules / denormalization. But normalisation principles are also there for a reason, to maximise data integrity and so on and can't be completely discounted, there's a reason by default the 1st normal form says to have a single value per cell (suggesting that a list of values in a cell isn't so much of a good thing to do, but of course there are trade-offs and necessary exceptions) -
So my question is, how do I ensure that I don’t ‘overdo’ denormalization via my use of array/list structures in tables. I am trying to find the balance between the two techniques of normalisation and denormalisation, as they are both beneficial in some way. I don’t want it to be the case where because I have this functionality via Postgres, I just use it freely or when it's convenient or when I feel like it - when the foundational, default practice is to not have an array of items in a cell, but for justifiable exceptions.
So I’m trying to use it sparingly and only where necessary, staying in line with best practice. I’m looking to find out what are those necessary, permissible, justifiable use cases to use array structures for a field/column.
For example, if I have a gallery for each one of my users, surely the most practical thing to do is to have an image field (say in the user table) - of array/list structure type.
But I’m aware that you’d never want an array with like 100+ items and growing.
I watched this tutorial with Sean and Prakash (https://www.youtube.com/watch?v=H1JBOJn6LDA) and it was said that if you’re working with a set of small limited data, it is best practice to use a ‘local array’ as it's more convenient, easier to access and view/display - so the data should be ‘localised’. For example, a thing that is either close to being, or is a fixed amount of items like ‘categories’.
Overall, I would just like a bit more clarity and some additional opinion on the topic of staying in line with database best practice when it comes to using array structures.
Some smaller side questions
In the video they created a local array in their user table for user roles but I wonder why not use an enum instead and have options like ‘Driver’, ‘Admin’, ‘Driver and Admin’ because then this would achieve the same function while satisfying 1NF?
About Enums
In a column of type enum - for an enum value in a given cell, is the cell just referencing the value from the enums list of possible values? Or is the value in the cell a standalone copy of an enum value from the list? I’m just trying to gauge data redundancy here.
Thanks, and apologies if this post is too long.