4 ms·

Ok, serious question. I have been writing SQL for 7 years now and can normalize schemas with the best of them; but I am wanting to learn the proper techniques f

Ok, serious question. I have been writing SQL for 7 years now and can normalize schemas with the best of them; but I am wanting to learn the proper techniques for creating scalable data sets like what one would store in GAE BigTable or EC2 SimpleDB... Let's say I have a book review site. In my SQL world I have tables of 'users', 'authors', 'books', and 'reviews'. When a user creates a review, it creates a new row in 'reviews' which contains the userid, bookid, and rating. Throw some JOINs together and I can pull out rows that have the user data, author data, book data, and the rating all together. What do you do in non-normalized world? When a user creates a review, just insert a row into a 'review' table that has user_id, user_name, author_name, book_title, book_isbn, book_etc, ..., rating ?? I can see how this would be a much more scalable table and fetching a review is just one row, but it just seems like so much duplicate data...? Or maybe I am thinking about it the wrong way?

riffraff 16y ago
you're not, denormalization implies duplication (though not all normalizations are created equal). It also usually implies that you have to replicate DB functionalities in the client (e.g. write data in two places yourself). But denormalization is not only a mean to "infiniscale" storage, you can use it even in smaller scale to just get better performances in certain things. As usual, everything is a trade off.
jules 16y ago
You'd probably do the same thing expect you add a column users.reviews and books.reviews that are lists of ids. For example Joe.reviews would contain a list of reviews by Joe. Essentially you're doing what a good database would do for you...
thezilch 16y ago
Your review table can still only contain the user_id, book_id, and rating. While using an RDBMS, instead of using joins, you want to get the user and book as separate objects anyway. The three objects (user, book, and review) should be cached separately, so you can use them in the duplicate places around your site. Of course, where you have a list of items to get users or books from, you will want to query your cache and DB using multi_get or in (id_1, id_2, ..). The idea behind these scalable sets are that we end up caching our data differently than how we store the same data in a relational set. Later, our code is checking our cache, first, in one fashion and then the relational store differently; why not query both the same? We can now "simplify" how we think about and consider our data models, whether it be a k:v, document, graph, or otherwise. We end up building these models on top of the RDBMS anyway, in our caching layer.

riffraff 16y ago

you're not, denormalization implies duplication (though not all normalizations are created equal). It also usually implies that you have to replicate DB functionalities in the client (e.g. write data in two places yourself). But denormalization is not only a mean to "infiniscale" storage, you can use it even in smaller scale to just get better performances in certain things. As usual, everything is a trade off.

jules 16y ago

You'd probably do the same thing expect you add a column users.reviews and books.reviews that are lists of ids. For example Joe.reviews would contain a list of reviews by Joe. Essentially you're doing what a good database would do for you...

thezilch 16y ago

Your review table can still only contain the user_id, book_id, and rating. While using an RDBMS, instead of using joins, you want to get the user and book as separate objects anyway. The three objects (user, book, and review) should be cached separately, so you can use them in the duplicate places around your site. Of course, where you have a list of items to get users or books from, you will want to query your cache and DB using multi_get or in (id_1, id_2, ..). The idea behind these scalable sets are that we end up caching our data differently than how we store the same data in a relational set. Later, our code is checking our cache, first, in one fashion and then the relational store differently; why not query both the same? We can now "simplify" how we think about and consider our data models, whether it be a k:v, document, graph, or otherwise. We end up building these models on top of the RDBMS anyway, in our caching layer.