SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: database design

  1. #1
    SitePoint Addict DevilBear's Avatar
    Join Date
    Oct 2001
    Location
    Hades
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    database design

    I've come up against something I never learned how to handle in my database classes. Hopefully some of you SitePointers know the way.

    Here's a general example to illustrate:

    event (id, date, name, sponsor_id)
    sponsor (id, name, etc)

    Some events have sponsors and some don't. I want to generate a list of all events in a month...

    select date, event.name, sponsor.name
    from event, sponsor
    where event.sponsor_id = sponsor.id

    means that events without a sponsor don't show up. Adding on

    or event.sponsor_id is null

    creates havoc

    What I've been doing is creating the first sponsor with id=0 and setting the rest of the sponsor fields to none, then setting the sponsor_id to default to 0... but it seems like that just can't be the best way of handling the issue.

    Any takers?

  2. #2
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was really curious about this too and I found this on PHPbuilder:
    http://www.phpbuilder.com/forum/read...5&thread=25561
    A LEFT JOIN differs from a 'normal' JOIN in that a LEFT JOIN will allways add the columns from the RIGHT table to the LEFT, even if there are no records in the RIGHT table that match the queries requirements.

    If you do

    SELECT * FROM table1 LEFT JOIN table2 ON foo=bar

    then the results will always contain all columns of both table1 and tale2, but the columns of tabl2 will contain NULL if there was no match on 'foo=bar'
    Does that do it?
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  3. #3
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Some events have sponsors and some don't.
    You seem to need three tables:
    event (id, date, name)
    event_sponsor(eventid, sponsorid)
    sponsor (id, name, etc)

    This way an event can have 0 or more sponsors

    select e.name, isnull(s.name, 'has no sponsor')
    from event e left join
    (event_sponsor es inner join sponsor s
    on es.sponsorid = s.id)
    on e.id = es.eventid

    (Think isnull() is a ms sql specific non standard function)
    Last edited by jofa; Jun 9, 2002 at 09:45.

  4. #4
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I meant to mention that too.

    With the way jofa did it, you could also easily have 2+ sponsors for one event, as needed.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  5. #5
    SitePoint Addict DevilBear's Avatar
    Join Date
    Oct 2001
    Location
    Hades
    Posts
    301
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry about the confusion. I was completely useless by the time I decided to seek some help on the problem. I went to bed after posting

    Thanks for the examples... I really meant to have only a one to many relationship.

    Now the real problem is, I have a table like so:

    table(id, stuff, etc, rel1, rel2, rel3, rel4, rel5)
    rel1 (id, stuff)
    rel2 (id, stuff)
    etc.

    At first the goal was to simply have the main table have 0 or 1 of each of the others. And this would be no problem... I could left join every one of them in.

    But what if I wanted one or two of them to be many to many, took their corresponding id columns out of the main table, and created table_rel# tables to connect them....

    I can't get jofa's code to work with MySQL, so this may not be possible for me.


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
  •