If you don't use joins, how do you associate records from two different tables when displaying the UI? Do you just join in the application? Or something else?
if you ask Amazon, they might suggest that you design around a single table (https://aws.amazon.com/blogs/compute/creating-a-single-table...).
in my opinion it's easier to use join tables. which are what are sometimes temporarily created when you do a join anyways. in this case, you permanently create table1, table2, and table1_join_table2, and keep all three in sync transactionally. when you need a join you just select on table1_join_table2. you might think this is a waste of space, but I'd argue storage is too cheap for you to be thinking about that.
that being said, you really have to design around your access patterns, don't design your application around your schema. most people do the latter because it seems more natural. what this might mean in practice is that you do mockups of all of the expected pages and what data is necessary on each one. then you design a schema that results in you never having to do joins on the majority, if not all, of them.
Great suggestion! I had a role where I helped a small team develop a full stack, data-heavy application. I felt pretty good about the individual layers but I felt we could have done a better job at achieving cohesion in the big picture. Do you have any resources where people think about these sorts of things deeply?
> We have suggested using denormalization as an intermediate step between logical and physical modeling, to be used as an analytic procedure for the design of the applications requirements criteria ... The guidelines and methodology presented are sufficiently general, and they can be applicable to most databases ... denormalization can enhance query performance when it is deployed with a complete understanding of application requirements.
PDF: https://web.archive.org/web/20171201030308/https://pdfs.sema...
An alternative I've heard before is using triggers with regular tables, so updating one automatically updates the relevant other ones.
As an added data point I don't really like programming books but bought this since the data out there on Single Table Design was sparse or not well organized, it was worth every penny for me.
https://www.alexdebrie.com/posts/dynamodb-single-table/
Im really curious about real life performance on different databases, especially in situation where RAM is smaller than database size.