So the other day I was uploading pictures into Facebook and I thought of this. I haven't done a lot of SQL but capable of making it work, I'd like to hear expert ideas and techniques for dynamic data entries.

So on Facebook you can tag people on photos you uploaded. I believe you can tag as many people as you please.

So how would you structure something like that? Have a table called photo_tags and just add entries for every tag? Would that overload the database image 1 Million photos with an average of 3 tags per photo, that's around 3 million entries. I think Facebook's photo database is in the billions.

How would a SQL database be structured for something like this or possibly on a smaller scale?

Well, the world of db performance tuning is a lot based on reducing the joins you need between tables.

Big sites, often have a "flat" architectures, which make that every informations are stored in 1 row. You might have heard of such tables as "materialized views".

For smaller system though, I'd still recommend to go the normalized way.
And as you said, having 1 table for the images, and another one for the tags, with a 1 to N foreign key from the tags to the images looks like the way to go.

Originally Posted by tripy

Well, the world of db performance tuning is a lot based on reducing the joins you need between tables.

Big sites, often have a "flat" architectures, which make that every informations are stored in 1 row. You might have heard of such tables as "materialized views".

For smaller system though, I'd still recommend to go the normalized way.
And as you said, having 1 table for the images, and another one for the tags, with a 1 to N foreign key from the tags to the images looks like the way to go.

little late but didn't even noticed somebody replied to this... thanks tripy, was very curious as to how things were moving forward

Well indexing and normalization are used to tune up the data . Even there would be mechanism to add dynamic column in the photo table for e.g
PHOTO phot_tag_id1 phot_tag_id2 phot_tag_id3
kind of it helps to access the data and tag information can be stored in different table

OMG, don't even think about db structure on sites like facebook or anything else that has moe than 1M users. It is literally magic, not less. Denormalization, partitioning, sharding, clustering and so on. Everything is done to retrieve data as fast as possible. And of course no joins at all.

I think its just nice to know some various structuring styles... its more like an art, you can do it in many ways that works

Originally Posted by mtishetsky

OMG, don't even think about db structure on sites like facebook or anything else that has moe than 1M users. It is literally magic, not less. Denormalization, partitioning, sharding, clustering and so on. Everything is done to retrieve data as fast as possible. And of course no joins at all.

Yes, make data available quickly and storing data efficiently is typically a multi-tiered approach depending on each subsystem of the website in question.

Big sites use data stores ( like google's bigtable), memory caching, and RDB to manage all the data for their sites.

A good website to learn what the big boys are doing is High Scalability.... really does a good job to highlight some of the strategies that can be used with various systems in your own site.