5 ms·
this has opinionated answers. if you ask Amazon, they might suggest that you design around a single table (https://aws.amazon.com/blogs/compute/creating-a-sing
by endisneigh 3y ago
this has opinionated answers.
if you ask Amazon, they might suggest that you design around a single table (https://aws.amazon.com/blogs/compute/creating-a-single-table-design-with-amazon-dynamodb/ 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.
- sainez 3y ago> 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?
- walterbell 3y ago2001, "Denormalization effects on performance of RDBMS", by G. L. Sanders and Seungkyoon Shin, https://www.semanticscholar.org/paper/Denormalization-effects-on-performance-of-RDBMS-Sanders-Shin/7643e5a175693ca07eff70e933214a67f216186f https://www.semanticscholar.org/paper/Denormalization-effect... > 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.semanticscholar.org/2c79/069c01ba8d598f32e61fe367ef6d261a0cb4.pdf https://web.archive.org/web/20171201030308/https://pdfs.sema...
- endisneigh 3y agoyeah, exactly. in my experience the vast majority of access patterns are designed around a normalized schema, where it really should be that the schema is designed around the access patterns and generously "denormalize" (which doesn't make sense in this context of a new database) as necessary.
- r00fus 3y agoWould it be possible to simply use a materialized view for table1_join_table2?
- Izkata 3y agoThe problem there would be how they fall out of date, and updating them is a heavy operation. An alternative I've heard before is using triggers with regular tables, so updating one automatically updates the relevant other ones.
- r00fus 3y agoMaterialized views allow insert/update mechanics on many platforms. Alternatively you can refresh them.