Hi,

 

I am working on a web application for college where I must design a web application that works like flickr (i.e. create account and upload/share photos)

 

Business Rules / Functionality

Registered Users:
•	Create photo albums.
•	Upload photos to albums.
•	Manage photos and albums (Create/Rename/Delete/Description)
•	Set album privileges (Private or Public)
•	Share albums (Send links with personal message to e-mail addresses)
•	Categorize photos (user defined tags or predefined categories)
•	Set to enable, disable or enable (with approval) of comments.
•	Rate users own photos and other user’s photos.
•	Manage account (Change preferences/passwords etc..)
•	Upload photos from mobile phone. (To be confirmed)
•	Have a profile page containing personal info e.g. Hobbies/Music/Movies etc. (Can also be set to private or public)

Administrators:
•	Can moderate all photos – Choose to delete any photos of their choice.
•	Receive reports of offensive photos/comments by users (maybe only registered users??)
•	Can delete account of report offenders.

Front page of site:
•	Search photos – by tags, categories, description.
•	Sign up – new account.
•	Sign in – existing accounts.
•	Show random photo of the day [large].
•	Top 5 most popular photos – by rating [small].
•	Total number of photos stored.
•	Total number of registered users.

 

Here is a simple diagram of what I envisage the database might look like for the application. My design skills are bad at best and I know this isn't bery good but it's a start.. I've also noticed that the album table needs to be altered to link the users and photos together. I think that albumId would be an int that is not unique.

 

 

Any thoughts suggestions on this?

 

Thanks,

--Makko

I'm probably talking rubbish but ...

 

I would make the users have ID, username and password and create a profile for each user?

(something like the diagram below)

I'm probably talking rubbish but ...

 

I would make the users have ID, username and password and create a profile for each user?

 

I had a quick google and saw the db schema for the phpbb forum.. they had the username and password included in the userdetails table.. thats one table (like your profile table) that included username and password also.

 

tbh that was the least of my worries..

 

What do you think of the rest of the design?

 

I have changed the album setup now, I have no diagram as I am away from my computer atm.

 

There is an albumDetails table tht stores name,description,album_id (primary key) and user_id(foreign key)

 

Then there is an albums_display table that links albums and photos:

albumsDisplay has got three foreign keys: album_id, user_id and photo_id

 

 

What do you think?

 

I have gone and created this database without any normalization because I am unable to do that. Am I making a big mistake?

 

Thanks..

You have the right idea but there are some issues regarding your cardinalities, however.

 

Your 1:1 between users and loginDetails is bad practice and consequently redundant. What real benefit is gained from storing their log in details separate? Just put their password with their user details. Also - usernames create many issues. It would be better to use their email as a username as emails are unique and that would be a good argument for your college assignment.

 

You also have a 1:1 between comments and reportedComments. Think of it like this:

 

One comment could have many reports.

 

Not one comment has one report.

 

So therefore the cardinality is 1:N (comment:report).

 

There's also the issue of storing "tags" in albums. I trust you didn't do your normalisation? Tut tut mister. Tags should be a table of its own. One photo has many tags. Otherwise you will get repeating data in photo and albums.

 

That's all for now, I think.

 

Other than that, as I said. Nice work.

 

As for stats, give them a unique auto ID (INT, Not Null) and store relevant properties in there such as date, user id, photo id, etc. That's a start.

Your 1:1 between users and loginDetails is bad practice and consequently redundant. What real benefit is gained from storing their log in details separate? Just put their password with their user details.

 

I have changed this now so username and password are in the users table..

 

Regarding the username/e-mail suggestion, why can't I just mark the field name username as UNIQUE and just check for an exisiting username whenadding and report to the user.

 

You also have a 1:1 between comments and reportedComments. Think of it like this:

 

One comment could have many reports.

 

Not one comment has one report.

 

So therefore the cardinality is 1:N (comment:report).

 

Agreed on this.. My first thoughts were that each comment/photo could be reported only once and each subsequent report by user would just add to a counter.. I thought this would just be easier and save on space.

 

But I suppose if I do it your way I can store a little bit of text from the user who is reporting the comment, describing why it is bad.

 

--

And for tags do you mean for tags photo/album:tag ratio is 1:N ?

 

So, we give way for a tags table that has an auto id and say a text field to store comma seperated tags?? Then use php to split? or would we store each tag in a seperate field, wouldn't that mean another table??

 

then each photo and album has a foreign key to to tag id?

 

 

Thats some great input TomCash, thanks..and Nero also!!

Regarding the username/e-mail suggestion, why can't I just mark the field name username as UNIQUE and just check for an exisiting username whenadding and report to the user.

Well, you can have a username as a unique ID. Lots of places do, I just think it easier to use email, personally. It was just a recommendation, not a rule of thumb.

 

Agreed on this.. My first thoughts were that each comment/photo could be reported only once and each subsequent report by user would just add to a counter.. I thought this would just be easier and save on space.

If you want to keep your format the same, you could always have the following table:

 

comment_id

comment_user_id

comment_photo_id

comment_comment

comment_report (INT, Default = 0)

 

Then, everytime it is reported, increment the comment table's comment_report field by one. Then you run a query that basically says:

 

"SELECT comment_report FROM tbl_comment WHERE comment_report != 0"

 

This way, you save space like you had originally planned.

 

And for tags do you mean for tags photo/album:tag ratio is 1:N ?

Yup.

 

So, we give way for a tags table that has an auto id and say a text field to store comma seperated tags?? Then use php to split? or would we store each tag in a seperate field, wouldn't that mean another table??

Not quite. Create a tags table and give it one field: tag_id, or tag, whatever. Then when the user is adding a picture, they can select a tag from a list (auto generated from tbl_tag) or they can add tags to tbl_tag. Ensure you make them unique however.

 

Then elsewhere on the website, you can relate the tags to photos and albums. This makes for a better user experience. Tonnes of websites do it. Every time you're on a site and you see "Tags: Animals, Cars" and you click one - that's how it's done. It's what I do on http://www.whotoseelive.com. I have a genre table and I use foreign keys to tie pages together.

 

Thats some great input TomCash, thanks.

Not a problem mate. I enjoy database design.