CODEWITHSUNDEEP
database-design
sudeep
sudeep

Reputation: 170

Need Help with database design

I'm building a basic photo uploading system which works like:

  1. User registers by providing very basic information (unique username, email and password).
  2. Registered users can upload photo with caption and tags(the photo name, directory, tags and caption is saved in database).
  3. Any registered user can add comment on the photo.
  4. I should be able to retrieve all tags of a username (which he used on all of his uploaded photos).

I need some help with the database design for it. I've made following 4 tables but I'm not sure if the design is correct. (In all of following tables, id is auto-increment primary key).

1: Users (id, Username, email, password)
2: Photo (id, username, photo_name, photo_directory)
3: Tags (id, Photo_id, username, Tag)  
4: Comments(id, photo_id, comment_author, comment_text, time)

Thank you very much for your time and help.

Upvotes: 2

Views: 183

Answers (5)

Rajesh Chamarthi
Rajesh Chamarthi

Reputation: 18808

I think the tag and Photo association should be a different association table (if i get the meaning of the tag right.)

A user can create a tag and associate it to multiple photos. So, you should probably have..

Photo (photo_id, User_id, photo_name, photo_directory)
Tags (tag_id, Tag_name, tag_desc, tag_attribute1)
photo_tag_asc (photo_id, tag_id).

This way, you can define a tag only once and avoid duplicates. This is more accurate if you want a good relational design.

Also, since the first column is a primary key, I would suggest using column names like photo_id, tag_id instead of just "id". I understand they will have table-name prefixes, but looking at the table name in the query for every column name is not very neat.

select p.photo_id, u.user_id
  from photo p, user u
   where p.user_id = u.user_id

is much easier to read than

select p.id, u.id
  from photo p,
       users u
  where u.id = p.user_id

Upvotes: 2

Philip Kelley
Philip Kelley

Reputation: 40289

This just consolidates several prior posts (all upvoted) into one place, along with a few minor tweaks.

Users (id, Username, email, password, created_at)
Photo (id, User_id, photo_name, photo_directory, uploaded_at)
Tags (id, tag_name, tag_description)
PhotoTags (photo_id, tag_id)
Comments (photo_id, User_id, comment_text, created_at)

This presumes that tag definitions can be used by multiple users (and so are not "owned" by any user), and that users can only "tag" their own photos.

Upvotes: 1

a1ex07
a1ex07

Reputation: 37354

You don't need to store username in Photo, Tags, Comments. Whenever you need a reference to another table, it should be a primary key in that table (user_id in Photo should refer to Users.id in your case).
Also, having a column for User_id in Tags seems superfluous since you already store a reference to Photo which has User_id.

Upvotes: 1

Shankar Raju
Shankar Raju

Reputation: 4546

Your design seems good to me, except that I don't think you need to have username/user id in the relation: Tags. Since I feel photo id and user id have one-one relationship. Other than that the design is good to me.

Edit

Also make use of id instead of name in other relations as mentioned by Brian Roach

Upvotes: 0

Brian Roach
Brian Roach

Reputation: 76888

You're really not making use of your relations:

Users (id, Username, email, password)
Photo (id, User_id, photo_name, photo_directory)
Tags (id, Photo_id, User_id, Tag)  
Comments(id, photo_id, User_id, comment_text, time)

You only want to store the id from the corresponding tables, and join to get the information you want.

Upvotes: 2

Related Questions