SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Double lookup table?

    I have three main tables:
    1. Businesses (name, description)
    2. Locations (address, contact info)
    3. Events (name, date, time)
    Every business has many locations. Events may be held at all locations, or only at specific locations.

    User has two options: add an event for all locations or choose the specific locations. If user chooses to add the event to all locations and more locations are added later, then events must propogate to the newly added locations.

    Right now I have a lookup table that has 3 columns:
    1. Event ID
    2. Location ID
    3. Business ID
    If the event is held at all locations, then I store Event ID and Business ID. Location ID remains NULL.

    If the event is held only at specific location(s), then I store Event ID and Location ID. Business ID remains NULL.

    The trick is that events will be listed all together, regardless if they are held at all or specific locations. I feel like it's an ugly solution and there must be something better. What do you think?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by moltar
    The trick is that events will be listed all together, regardless if they are held at all or specific locations.
    that is REALLY easy, and could be accomplised with a UNION or a LEFT OUTER JOIN.

    but your question sounds too much like homework for me to simply provide oyu the answer.

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    but your question sounds too much like homework for me to simply provide oyu the answer.
    Heh. For some reason I thought that someone is going to say that when I was writing the question. It does sound like something a teacher would write. But the truth is - this is not homework.

    I forgot to mention an important piece of information. I am using DBIx::Class - perl OO module for data access. It's not easy to do UNION and other complex queries. That is why I need to rely on a solid structure to accomplish this. I can solve this in vanilla SQL with the JOINs no problem. I am just contemplating the correct database structure.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i think that will work, but it still makes me somewhat apprehensive that something could come along and upset it

    instead of your lookup table with those 3 keys, how about removing the business id --

    eventlocations
    event_id
    location_id

    this table would be populated only for those events which are held only at specific locations

    then in the event table, place the foreign key to the business

    if an even has no eventlocations rows, then obviously it's gotta be an event for all locations for that business

    i like this better because all the PKs are not null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Canada
    Posts
    108
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, once again you come up with an elegant solution

    I am definetely going to do it that way! Next time I'll be in T. - I must buy you a beer!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    thanks for the kind words, yes, i like beer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •