How does Snapchat store its data

database-design

I am a highschool student who is interested in developing apps and learning more about databases and servers, so please bear with me if this is the wrong place to ask or if it's a obvious answer.

I have developed a few small database iPhone apps with Parse.com, so what somewhat of an idea of how a database works. But of course, I'm sure that is nothing compared to a real database.

My question is: How does an app like Snapchat go about storing its data (do you think)?

Does it just have one huge table of all the images? Meaning when someone uploads and app, does it create an object/row in a large table with the picture, who sent it, recipients, date, etc.? And then when the user logs in, does the app query that large table for all objects in that large table that has the currently logged in users id associated with recipients?

Talking very high-level, conceptual. This is my thought of how it would work, but I know there is millions of pictures on Snapchat servers, so I'm not sure how the speed of querying a large table like that would work, or how else it would be organized.

Best Answer

All good, future-proofed databases are normalised to some extent. Normalisation is described by Louis Davidson ~ Pro SQL Server 2008 Relational Database Design and Implementation as

The process of normalization is really quite straightforward: take entities that are complex and extract simpler entities from them. The process continues until every table in the database represents one thing and every column describes that thing.

So based on your example I would imagine that the Snap Chat database (assuming that we are talking about OLTP model and not using NOSQL / BIGData) would have multiple entities, with each entity containing relevant data and be related to other entities via the appropriate relationship i.e. 1..Many, Many to Many etc. For example, a person entity might contain UserName and Password and be related to a loginToSite entity via a 1..M relationship. I suggest a 1..M relationship because one person can log in to the site multiple times but each login to site record will only be related to one user. If all attributes were stored in the a single entity then in this example the UserName and Password information would be duplicated per Login record and break the rules of normalisation. This would also lead to a database usability nightmare!!

Depending on business rules, and these would be questions you would have to ask snap chat. One user record might be related to one or many photo records or maybe one user can be related to one or many photo records and one photo record can be related to one or many user records. In this case, there would be a many to many relationship.

When thinking about database design it's important to first work out the conceptual design by talking with end users, reviewing reports / spread-sheets (anything containing data used by the end user) and generating an ERD. Then once the relationships have been agreed, all data to be stored captured and the ERD modelled you can move onto the physical side of things and work on performance, choosing data types, deciding on indexes etc...

Related Solutions

Should I use a separate table to store image file names

If each image has a specific purpose then this would be OK but your column names should be more specific, such as img_thumbnail, though I would still use a separate table with columns that carry information about each images use.

The better design, especially if the images do not have specific well differentiated uses, is to follow first normal form and have the images in a separate table. This allows cleaner storing of extra data about the images should you need to later, and so forth.

Is Temporary Table Good for Importing Temporary Data into Database?

In your case, temporary tables could prove to be a challenge.

Specifically, temporary tables (created by CREATE TEMPORARY TABLE ...) are bound to the current database connection, which can be difficult to maintain with PHP - a single web session could use one or many database connections.

I would suggest creating a static table in your database with extra columns, for example:

Temporary student list
web_user_id, matriculation_number, first_name, last_name

This one would allow each user to have a temporary student list, which would persist across database connections and would behave nicely with PHP. The data could exist between user logins if that was useful.

Temporary student list
web_session_id, matriculation_number, first_name, last_name, created_date

This might be more useful if you don't want to attach the list to a particular user. PHP's session handle should be more persistent than a database connection(s), and the created_date would be useful to allow you to do some periodic or automatic cleanups (e.g. get rid of anything older than a day).

Alternatively, you could store nothing in the database. You could create your query entirely within in PHP, and pass the student IDs inline:

SELECT columns FROM STUDENT INNER JOIN EXAM ON STUDENT.ID = EXAM.STUDENT_ID WHERE STUDENT.MATRICULATION_NUMBER IN (123, 234, 345, 456, 567...)

Best Answer

Related Solutions

Should I use a separate table to store image file names

Is Temporary Table Good for Importing Temporary Data into Database?

Related Question