CODEWITHSUNDEEP
mysqldatabase-designrelationships
Moak
Moak

Reputation: 12885

Am I going overboard with MySql tables?

While developing I am adding databases with each major addition. My latest is an image gallery program, I'm asking myself all these questions/

A simplified version of possible tables

Image (id, name, path, state)
Gallery (id, name, path, state)
Image Gallery Relationship (image id, gallery id)
Image User Relationship (image id, user id)
Image Venue Relationship (image id, venue id)
Image Event Relationship (image id, event id)
Gallery User Relationship (image id, user id)
Gallery Venue Relationship (image id, venue id)
Gallery Event Relationship (image id, event id)

I'm thinking the same photo can be owned/taken by a user, assigned to an event and to a venue, in a gallery from the users photos, in a gallery related to the event, in a gallery related to the venue.

Now if I want to use the same database to store the profile picture of a user, would I create another table for that? Should I put the profile picture id into the user table? Should I put the photo ownership into the Image table? But most likely a major part of the pictures won't be uploaded by users. What about the "profile picture" for a an Event or venue? Or the cover picture of a a gallery? Am I creating to many tables or not enough?

sidenote Generally when creating columns I think if it is a required, single value attribute then place it in the table, If it could be null or associated with multiple things it should probably be in another table. But I'm at 60 tables now and I'm not even half way through my program.

These day's I'm really lacking a mentor, so all advice is greatly appreciated

Pic related, it's my current database my current database

also If my question can be more generalized to help future people with similar questions I don't mind edits

Upvotes: 0

Views: 233

Answers (2)

Xavier Holt
Xavier Holt

Reputation: 14619

It all depends on the exact relationships between all your objects. Your description was a little bit fuzzy, so I'm going to be making some assumptions here, but here's what I get out of it:

Main rule: Only use join tables (like Gallery_Image_Relationship (gallery_id, image_id)) when you have a many-to-many relationship; I suspect gallery-image is a good example. A gallery contains many images, and an image can belong to many galleries. For anything else, though, they're just needless complication.

For the rest of your relationships, just put a foreign key in one of your tables. If it's one-to-many, the table is obvious: A user can upload many images, but an image can only be uploaded by one user? Then your images table gets an uploader_id column.

That said, here's what that would turn into in your DB:

users:
  id,
  name,
  profile_image_id, #A user can only have one profile pic (at a time)
  etc.

images:
  id,
  name,
  uploader_id, #It can only be uploded by one person
  etc.

galleries:
  id,
  name,
  cover_image_id,
  etc.

images_galleries: # 'cause images:galleries is many:many
  image_id,
  gallery_id

(I have no idea what language SO thinks that was. It highlighted user, by, is, and the apostrophe...)

It gets more subjective when you're trying to tie images to venues and/or events. Do you expect most/all of your images to be of a particular event? If so, then you'll probably want an event_id column in your image table. Same goes for venues.

But if you're expecting lots of images not to be of any specific event, then those columns would just be a waste of DB space. So would a gallery typically be tied to a single event/venue? If so, then that's where your foreign keys belong.

And if neither of those fit too well, then your join tables are still an option. I doubt the normalization diehards would approve, but sometimes you don't need to be picky.

Hope that helps!

Upvotes: 1

thf
thf

Reputation: 604

Whatever you decide, you should frame that and hang it in the gallery.

You may want to simplify it. Look at it differently: consider one central things table to which the image might be associated and then find common ground for those things. Add a type field (gallery, venue, event). Maybe you'll add an extra field or two, but in the end it will greatly simplify your work and the database. I'm going through the same soul searching on something I'm creating.

Upvotes: 2

Related Questions