Photo album table design

For photo 开发者_Go百科albums I see most people use 3 tables:

Albums Photos PhtoAlbums.

However if I look at a site like facebook this schema wont work (if I understand correct) because I may have a photo in my profile album and the general albums but I can give the same picture different descriptions, different tags, etc. Even the photo ID is different. So my guess is anytime a user creates a copy of a photo it is treated a a brand new photo and hence we need only two tables: Albums and Photos (which has FK to album)

Other options is have photo table with only 1 column (photo_id) and put all the photo details in the PhotoAlbum table so I can give each unique properties per Album.

Am I valid in this design?

Photo <- Photo_Album -> Album. Is the relationship, so put the various payload (description, tags etc) on the Photo_Album table (as well as the photo itself) so while a photo is in the database, each instance of the photo in an album is also tagged/described.

You can then be clever about how you merge/display this data to the user.

Also, don't forget Facebook isn't a relational model. It's a "NoSQL"-style database for scaling reasons, and works very differently. You can lookup "NOSQL" on Google for more information on that train of thought.

In a relational database the Photo/Photo_Album/Album is the normal way to store the data. The principle being that you store a photo (or single entity) only once but use other other mechanisms to provide the end user functionality you require.

You don't actually need to create a "copy" in the database of the photo to have different meta-data apply to it. You could have the meta-data (different tags, descriptions ect) in a table related to the Photo_Album table that contains this meta-data instead of keeping it in the Photo table. This way you would have different meta-data for each combination of a Photo and an Album.

By using this three (well technically four) table related approach you can have meta-data about just a Photo or just an album or a combination of the two.

It all comes down to your end state & potential future requirements, of course but the point of this design pattern is to treat your database as a separate, economical & optimised data storage system. You then use SQL/TSQL and many other "intermediate" mechanisms to provide the interface to your GUI.

n a relational database the Photo/Photo_Album/Album is the normal way to store the data. The principle being that you store a photo (or single entity) only once but use other other mechanisms to provide the end user functionality you require.

You don't actually need to create a "copy" in the database of the photo to have different meta-data apply to it. You could have the meta-data (different tags, descriptions ect) in a table related to the Photo_Album table that contains this meta-data instead of keeping it in the Photo table. This way you would have different meta-data for each combination of a Photo and an Album.

By using this three (well technically four) table related approach you can have meta-data about just a Photo or just an album or a combination of the two.

It all comes down to your end state & potential future requirements, of course but the point of this design pattern is to treat your database as a separate, economical & optimised data storage system. You then use SQL/TSQL and many other "intermediate" mechanisms to provide the interface to your GUI.You can create best photo album designing and making service at http://www.albumkart.com