4 ms·

The general rule is to normalize to the max, then denormalize till you get the performance that you need.

jayknight 2y ago
Anyone here use 6NF to avoid nulls? See https://36chambers.wordpress.com/2021/10/22/avoiding-null-with-normalization/ https://36chambers.wordpress.com/2021/10/22/avoiding-null-wi...
- cryptonector 2y ago
  Isn't 6NF essentially a flavor of EAV? I think essentially it is. 6NF means having one non-PK column, so that if the value would be NULL then the row need not exist, and so the value column can be NOT NULL. But when you need all the columns of what you'd think of as the <thing> then you need to go gather them from all those rows in all those tables. It's a bit annoying. On the other hand a proper EAV schema has its positives (though also its negatives).
  - jayknight 2y ago
    It's similar, but instead of one table holding lots of attributes, there are separate tables that hold optional fields that might otherwise be null if they were in the main table.
- magicalhippo 2y ago
  Every time I see a suggestion like this, I wonder what kind of projects people work on that only require a few columns. In my experience every non-trivial project would need several dozen columns in core tables, all which could be NULL while the user works on the record during the day. In our current project we'd have to do several hundred joins to get a single main-table record. I also wonder why people have such an aversion for NULL. Crappy tools?
  - jayknight 2y ago
    No, it's just a dedication to pure relational algebra. Nulls introduce "three valued logic" into your code/sql. A nullable boolean column can contain 3 different "values" instead of two. True is not false, true is not null, false is not null (and null is neither true nor false). Or in a nullable numeric field, you have rows where that column is neither greater than zero or less than zero. On the other hand, even if you eliminate all nulls, you still have to do some three-valued logic ("A join B on B.is_something", "A join B on not B.is_something" and the "anti-join": "A where no B exists"), but only in the context of joins. It feels a little like rust's Option type. If something in "nullable", these are tools that forces you to deal with it in some way so there are fewer surprises.
    - deleted 2y ago
      [deleted]
  - marcosdumay 2y ago
    AFAIK, the 6NF is exclusively for theory research.
DanHulton 2y ago
The one exception I'll make from the very start is "add tenant identifier to every row, yes, even if it's linked to another table that has tenant identifiers." Sure, this means you will have some "unnecessary" `tenant_id` columns in some tables that you could get through a relation, but it saves you from _having_ to include that relation just to limit by tenant, which you will some day almost be guaranteed to want. (Especially if you want to enable row-level security¹ someday.) ¹ - https://www.postgresql.org/docs/current/ddl-rowsecurity.html https://www.postgresql.org/docs/current/ddl-rowsecurity.html
- getcrunk 2y ago
  Nice one! I’m working on an app the is user/consumer facing but will eventually have a teams/bussiness offering. Everything has a uid with it cus users are the core of your app. But yea if your multitennant then tenants should be treated like that as well.
- magicalhippo 2y ago
  We do a variant of this across all our tables. If we have a parent-child-child relationship, then all child tables, regardless of depth, will have the parent id. This way we can load up all the data needed to process an order or invoice etc easily, without a ton of joins. We don't do multi-tenant, instead separate databases per tenant so far.
  - wadadadad 2y ago
    How is this working for you so far? Do you ever need to report across the multiple tenants, and how do database migrations go? I'm starting to look into this, and purely for reporting and database migrations I'm leaning towards multi-tenant.
    - magicalhippo 2y ago
      The product I'm working have not needed to report across multiple tenants. We do have some customers which have separate daughter companies which might technically be individual tenants, and where we might need to report across those. But in all those cases so far we've been able to host the databases on the same server, so can easily join tables from the individual databases into the same query. Database migrations are very smooth, given each tenant has their own database, so we can do it when they're ready instead of needing a service window that fits all our tenants. We have several that are essentially 24/7 operations, and while they do have periods of low activity that fits a service window, they usually don't line up well between them. Likewise schema upgrades are trivial. We have written our own little tool that updates the database given a source schema description in XML (since we've had it for 15+ years now), and as it does not do destructive updates is is low risk. So schema upgrades is done automatically as part of our automated update deployment (ala Windows Update). Of course this requires a bit more thought during schema design, and sometimes we do add some manual cleanup or similar of tables/columns to our tool that we know are safe to remove/change. One upside is that performance is easy to manage. A single tenant will almost never cause performance issues for others than themselves. If they start doing that we can always just trivially move them to their own server. A downside is that we have a lot of large code lists, and currently these are kept per-database as we've traditionally been deployed on-prem. So we're looking to consolidate those. We do have another product that I haven't worked on, it's a more traditional SAAS web app thing, and that does have multi-tenant. It's not as business-critical, so the service window constraint isn't an issue there. Given that it has an order of magnitude more tenants than the application I work on, I think that multi-tenant was a decent choice. However it has also had some complications. I do recall hearing some issues around it being a more critical point of failure, and also that certain larger customers have started to want their own separate database. I think ideally we would have combined the two approaches. Allow for multi-tenant by having a tenant id everywhere, but also assume different tenants will run in different databases.
marcosdumay 2y ago
Oh, no, it's absolutely not. It's usually to normalize into the 3rd form. But that's not enough on some cases, that's too much on some other cases, and the reason it breaks is performance about as often as it's not.
yen223 2y ago
"normalise til it hurts, denormalise til it works"

Anyone here use 6NF to avoid nulls? See https://36chambers.wordpress.com/2021/10/22/avoiding-null-with-normalization/ https://36chambers.wordpress.com/2021/10/22/avoiding-null-wi...

cryptonector 2y ago
Isn't 6NF essentially a flavor of EAV? I think essentially it is. 6NF means having one non-PK column, so that if the value would be NULL then the row need not exist, and so the value column can be NOT NULL. But when you need all the columns of what you'd think of as the <thing> then you need to go gather them from all those rows in all those tables. It's a bit annoying. On the other hand a proper EAV schema has its positives (though also its negatives).
- jayknight 2y ago
  It's similar, but instead of one table holding lots of attributes, there are separate tables that hold optional fields that might otherwise be null if they were in the main table.
magicalhippo 2y ago
Every time I see a suggestion like this, I wonder what kind of projects people work on that only require a few columns. In my experience every non-trivial project would need several dozen columns in core tables, all which could be NULL while the user works on the record during the day. In our current project we'd have to do several hundred joins to get a single main-table record. I also wonder why people have such an aversion for NULL. Crappy tools?
- jayknight 2y ago
  No, it's just a dedication to pure relational algebra. Nulls introduce "three valued logic" into your code/sql. A nullable boolean column can contain 3 different "values" instead of two. True is not false, true is not null, false is not null (and null is neither true nor false). Or in a nullable numeric field, you have rows where that column is neither greater than zero or less than zero. On the other hand, even if you eliminate all nulls, you still have to do some three-valued logic ("A join B on B.is_something", "A join B on not B.is_something" and the "anti-join": "A where no B exists"), but only in the context of joins. It feels a little like rust's Option type. If something in "nullable", these are tools that forces you to deal with it in some way so there are fewer surprises.
  - deleted 2y ago
    [deleted]
- marcosdumay 2y ago
  AFAIK, the 6NF is exclusively for theory research.

DanHulton 2y ago

The one exception I'll make from the very start is "add tenant identifier to every row, yes, even if it's linked to another table that has tenant identifiers." Sure, this means you will have some "unnecessary" `tenant_id` columns in some tables that you could get through a relation, but it saves you from _having_ to include that relation just to limit by tenant, which you will some day almost be guaranteed to want. (Especially if you want to enable row-level security¹ someday.) ¹ - https://www.postgresql.org/docs/current/ddl-rowsecurity.html https://www.postgresql.org/docs/current/ddl-rowsecurity.html

getcrunk 2y ago
Nice one! I’m working on an app the is user/consumer facing but will eventually have a teams/bussiness offering. Everything has a uid with it cus users are the core of your app. But yea if your multitennant then tenants should be treated like that as well.
magicalhippo 2y ago
We do a variant of this across all our tables. If we have a parent-child-child relationship, then all child tables, regardless of depth, will have the parent id. This way we can load up all the data needed to process an order or invoice etc easily, without a ton of joins. We don't do multi-tenant, instead separate databases per tenant so far.
- wadadadad 2y ago
  How is this working for you so far? Do you ever need to report across the multiple tenants, and how do database migrations go? I'm starting to look into this, and purely for reporting and database migrations I'm leaning towards multi-tenant.
  - magicalhippo 2y ago
    The product I'm working have not needed to report across multiple tenants. We do have some customers which have separate daughter companies which might technically be individual tenants, and where we might need to report across those. But in all those cases so far we've been able to host the databases on the same server, so can easily join tables from the individual databases into the same query. Database migrations are very smooth, given each tenant has their own database, so we can do it when they're ready instead of needing a service window that fits all our tenants. We have several that are essentially 24/7 operations, and while they do have periods of low activity that fits a service window, they usually don't line up well between them. Likewise schema upgrades are trivial. We have written our own little tool that updates the database given a source schema description in XML (since we've had it for 15+ years now), and as it does not do destructive updates is is low risk. So schema upgrades is done automatically as part of our automated update deployment (ala Windows Update). Of course this requires a bit more thought during schema design, and sometimes we do add some manual cleanup or similar of tables/columns to our tool that we know are safe to remove/change. One upside is that performance is easy to manage. A single tenant will almost never cause performance issues for others than themselves. If they start doing that we can always just trivially move them to their own server. A downside is that we have a lot of large code lists, and currently these are kept per-database as we've traditionally been deployed on-prem. So we're looking to consolidate those. We do have another product that I haven't worked on, it's a more traditional SAAS web app thing, and that does have multi-tenant. It's not as business-critical, so the service window constraint isn't an issue there. Given that it has an order of magnitude more tenants than the application I work on, I think that multi-tenant was a decent choice. However it has also had some complications. I do recall hearing some issues around it being a more critical point of failure, and also that certain larger customers have started to want their own separate database. I think ideally we would have combined the two approaches. Allow for multi-tenant by having a tenant id everywhere, but also assume different tenants will run in different databases.

marcosdumay 2y ago

Oh, no, it's absolutely not. It's usually to normalize into the 3rd form. But that's not enough on some cases, that's too much on some other cases, and the reason it breaks is performance about as often as it's not.

yen223 2y ago

"normalise til it hurts, denormalise til it works"