In the DB Schema for instagram why do we need and extra table UserPhoto for storing relation between users and photos for NoSQL whereas for SQL we don’t need an extra table?
Why can’t we use the same photos table which also contain the UserID along with other details like we do for SQL Databases?
i was about to ask same question.It create lot of doubts.What i would suggest is that we dont need an y such extra table.One thing we can do is that they mentioned to store all photoid of a user in separete table.Well this is required for both sql and nosql.But it does not make sense to me.Extra table for faster search.We can create index on photos table , to allow search by user id.That is all based how much speed we need.
For NoSQL, we are not allowed to have ‘joins’ between tables. We need to store the data, in such a way, that facilitates our queries. Another thing to keep in mind, for NoSQL we can’t have any other column in the ‘where’ clause of the query other than the primary key.
NoSQL scales well, but we need to model our data in such a way that enables us to query the data the way we want it. In other terms, before design the data model for NoSQL, we need to know all of our queries beforehand.
@Design_Gurus , I saw dynamo db and we can query by creating secondary index.But the main question is , when photo has user id then it is sufficient to create secondary index and fetch based on user id. There are no joins present, in case joins are there , we will store data in denormalized form, but here to fetch data based on user id, we dont need join, the where class will be handled by creating secondary index.
Yes, we can have a secondary index, that’s an alternate; which is equivalent to creating a new table. The table ‘UserPhoto’ is like a secondary index to find photos of a user. By the way, not all NoSQL DBs allow secondary indexes and some don’t perform that well with secondary indexes.
Understood. Thanks,It all about exploring options and weighing.But then you can also use this table in SQL also. Because it will also increase speed as we will not create secondary index in photo talble. Since it is mentioned in book, that this extra table is needed for no sql, it caused confussion