3 ms·

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?

by iamwil 3y ago

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 ago
    2001, "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 ago
      yeah, 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 ago
  Would it be possible to simply use a materialized view for table1_join_table2?
  - Izkata 3y ago
    The 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 ago
      Materialized views allow insert/update mechanics on many platforms. Alternatively you can refresh them.
joshstrange 3y ago
Single Table Design is the way forward here. I can highly recommend The DynamoDB Book [0] and anything (talks, blogs, etc) that Rick Houlihan has put out. In previous discussions the author shared a coupon code ("HACKERNEWS") that will take $20-$50 off the cost depending on the package you buy. It worked earlier this year for me when I bought the book. It was very helpful and I referred back to it a number of times. This github repo [1] is also a wealth of information (maintained by the same guy who wrote the book). 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. [0] https://www.dynamodbbook.com/ https://www.dynamodbbook.com/ [1] https://github.com/alexdebrie/awesome-dynamodb https://github.com/alexdebrie/awesome-dynamodb
- deely3 3y ago
  And if you don't want to spend money, you can get idea from this article: https://www.alexdebrie.com/posts/dynamodb-single-table/ 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.
  - wizofaus 3y ago
    That article didn't appear to be suggesting single-table design was appropriate for general purpose RMDBSes (or any database other than DynamoDb).

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?

by iamwil 3y ago

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?

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 ago
  2001, "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 ago
    yeah, 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 ago
Would it be possible to simply use a materialized view for table1_join_table2?
- Izkata 3y ago
  The 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 ago
    Materialized views allow insert/update mechanics on many platforms. Alternatively you can refresh them.

joshstrange 3y ago

Single Table Design is the way forward here. I can highly recommend The DynamoDB Book [0] and anything (talks, blogs, etc) that Rick Houlihan has put out. In previous discussions the author shared a coupon code ("HACKERNEWS") that will take $20-$50 off the cost depending on the package you buy. It worked earlier this year for me when I bought the book. It was very helpful and I referred back to it a number of times. This github repo [1] is also a wealth of information (maintained by the same guy who wrote the book). 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. [0] https://www.dynamodbbook.com/ https://www.dynamodbbook.com/ [1] https://github.com/alexdebrie/awesome-dynamodb https://github.com/alexdebrie/awesome-dynamodb

deely3 3y ago
And if you don't want to spend money, you can get idea from this article: https://www.alexdebrie.com/posts/dynamodb-single-table/ 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.
- wizofaus 3y ago
  That article didn't appear to be suggesting single-table design was appropriate for general purpose RMDBSes (or any database other than DynamoDb).