SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database structure question

    A site I'm working on has an archive of mountain biking images, I'm trying to make the archive searchable by rider, location, etc.

    I have set up:
    a table for riders, name, team etc.
    a table for locations, region, town etc.
    a table for images... and here is the problem

    An image has one date, location, lensman etc. but can have any number of riders.

    So far I find I can either:
    1. Have multiple columns: rider1, rider2 ..... silly really
    2. Store numbers as a string with a seperating characters, e.g. -5- -12- -6- -7-. Then I can run searches such as WHERE rider LIKE -6-

    Second option seems ok but any other ideas on how I can structure the table to store varying numbers of rider ID's per image would be much appreciated.

    Does MySQL have a facility to store and search arrays or do I need to use it in conjunction with PHP?

    Regards,

    Steve

  2. #2
    SitePoint Guru
    Join Date
    Jan 2001
    Location
    Alkmaar, Netherlands
    Posts
    710
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have one more table called "link_riderimage"
    This extra table have fields ri_riderid, ri_imageid

    so image with image id 5 is connected to 2 riders with ids 2, 3 like
    ri_riderid | ri_imageid
    2 | 5
    3 | 5

  3. #3
    SitePoint Member
    Join Date
    Jul 2003
    Location
    UK
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that


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
  •