SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    advise in designing some tables

    Hello

    I am new to MySQL, and would appreciate your advise on what would be the best way to design the following small system (I am using MySQL 5.0)

    The system has photo albums which have their own data (name, category, submitter, date, number of photos)
    The system also has photos which have their own data (name, size, width, height, date, submitter...)
    The photos should be stored once in a big repository.
    Photos can be assigned to photo albums, in a many-to-many relationship:
    A photo album may contain multiple photos and a single photo may appear in multiple albums.
    The system has a set of categories that the albums can belong to. An album must belong to one of the defined categories.

    I would appreciate your help
    Thanks

  2. #2
    SitePoint Addict
    Join Date
    May 2004
    Location
    .
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You should have a table like this:

    id (for indexing purposes), album_id (that would contain the ID of a photo album the image is in), image_id (ID of an image from the images table).

    This way you can know what images are stored in each album and it would be possible to store one image in multiple albums.

    So you should have these tables:

    table: photos
    fields: id, filename, name, size, width, height, date, submitter [, other fields]

    filename is the name of a photo. Photos should be stored as files.

    table: categories
    fields: id, name [, other fields]

    table: albums
    fields: id, name, category_id, submitter, date [, other fields]

    table: main_list
    fields: id, album_id, image_id [, other fields]

    I hope this helps.
    Ilya Devyatovsky
    ThinkHost, Inc.
    Wind/solar powered web hosting - 6 months free!

  3. #3
    SitePoint Zealot
    Join Date
    Mar 2004
    Location
    united states
    Posts
    178
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the suggestion.

    can you explain what would be the advantages of adding the id attribute to the main_list table?
    I understand that this table allows for the many-to-many relationship between photos and albums. What is the id attribute for? What sort of indexing do you have in mind?
    I don't have any other attributes for the photo-to-album table, so I don't see a need for another attribute. Can you explain it a bit?

    thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the id in main_list is unnecessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    May 2004
    Location
    .
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, the id is unnecessary there, but it would be much simpler to handle items (photos in albums) in your application.

    It's just the way I would do it myself, nothing more
    Ilya Devyatovsky
    ThinkHost, Inc.
    Wind/solar powered web hosting - 6 months free!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •