Efficient Database Structure

Question

I'm working on an app where part of it involves people liking and commenting on pictures other people posted. Obviously I want the user to be notified when someone comments/likes their picture but I also want that user to be able to be able to see the pictures that they posted. This brings up a couple structuring questions.

I have a table that stores an image with it's ID, image, other info such as likes/comments, date posted info, and finally the userID of the user that posted the image:

Here's that table structure:

Image Posts Table: |postID|image|misc. image info|userID|

The userID is used to grab information from the users entry in the user table for notifications. Now when that user looks at a page containing his own posts I have two options:

1.) Query the Image Posts Table for any image containing that user's userID.

2.) Create a table for each user and put a postID of each image they posted :

Said User's Table: |postID|

I would assume that the second option would be more efficient because I don't have to query a table with a large amount of entries. Are there any more efficient ways to do this?

Obviously I should read up on good database design so do any of you have any good recommendations?

Answer 1

I would recommend against manually storing the userID, as Parse will do it's own internal magic if you just set a property called user to the current user. Internally it stores the ID and marks it as an object reference. They may or may not have extra optimizations in there for query performance.

Given that the system is designed around the concept of references, you should keep to just the two tables/classes you mentioned.

When you query the Image Posts table you can just add a where expression using the current user (again it internally gets the ID and searches on that). It is a fully indexed search so should perform well.

The other advantage is that when you query the Image Posts table you can use the include method to include the User object it is linked to, avoiding a 2nd query. This is only available if you store a reference instead of manually extracting and storing the userID.

Have a look at the AnyPic sample app on the tutorial page as it is very similar to what you mention and will demonstrate the ideas.

Answer 2

Multiple tables of identical structure almost never makes sense. Writing queries using your 2nd option would become ugly in short order. Stick with 1 large user's table, databases are designed to handle tables with many rows.