Subject: Struggling [Is : With database normalisation]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Probably something as simple as changing the structure to a more normalised form would help :

By adding a table which links evt_details to evt_sponsors, you do away with the need to have (up to 4) distinct fields to hold the evt_sponsor in your evt_details table.

So add something like table : `evt_sponsors_list`

`evt_detail_link_id`    INT 6
`evt_sponsor_link_id`   INT 6

Then your query becomes more like :

SELECT evt_detail_id, evt_detail_title, evt_sponsor_name
FROM evt_details
LEFT JOIN evt_sponsors_list
ON evt_detail_id=evt_detail_link_id
LEFT JOIN  evt_sponsors
ON evt_sponsor_link_id=evt_sponsor_id
WHERE evt_detail_date <= DATE_SUB(NOW(), INTERVAL 1 DAY)

The left join evt_sponsors_list will pull any number (1-many) of results where there is one or more matching sponsor. The left join evt_sponsors will then match those results to the evt_sponsors table, resulting in a series of rows for each event. You could change the 1st join to read 'LEFT OUTER JOIN' if you wanted to get events where there are *no* sponsors (what you were previously doing with `OR e.evt_sponsor_id_n =0`)

This is a one-to-many relationship and allows you to have flexibility for any number of event sponsors (not just 4) without adding more and more fields to your event_detail table. Make sure to index both columns in the evt_detail_link table for good performance, and the storage overhead is almost neglible.

You can then run a simple loop in PHP, cache the new row each time the evt_detail_id changes (containing the event title, the ID is useful so you can link to full details by event_id if you need to). Then for the 1st and subsequent rows, just use the evt_sponsor_name. Output the previous row when the evt_detail_id changes, and output the final row when there are no more results.

Cheers - Neil.


At 22:24 04/05/2004 +0000, you wrote:
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
From: "Erik Meyer" <emeyer@xxxxxxxxxxxx>
To: "PHP-DB" <php-db@xxxxxxxxxxxxx>
Date: Tue, 4 May 2004 17:24:59 -0500
Message-ID: <DFEPKAKJMAFKINPMJGNNEEEGCCAA.emeyer@xxxxxxxxxxxx>
MIME-Version: 1.0
Content-Type: text/plain;
        charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Subject: Struggling

Hope someone can help me.

I have two tables:

evt_details
evt_sponsors

The structure is as follows:

evt_details evt_sponsors

evt_detail_id (INT)(PK) evt_sponsor_id (INT)(PK)
evt_detail_title (CHAR) evt_sponsor_name (CHAR)
evt_sponsor_id1 (INT) NON-NULL
evt_detail_date (date)
evt_sponsor_id2 (INT) NULL
evt_sponsor_id3 (INT) NULL
evt_sponsor_id4 (INT) NULL


Now, the event can have either 1 sponsor, 2 sponsors, 3 sponsors, or 4
sponsors.

Is there a way where I can return a result whether it has 1, 2, 3, or 4
since some of the events will  not have a 2d,3d, or 4th sponsor, a
combination of 1 & 2, 1,2,3, or all 4 depending on the values in the table?

Here is the query I have:

SELECT e.evt_detail_title, a.evt_sponsor_name, b.evt_sponsor_name,
c.evt_sponsor_name,
       d.evt_sponsor_name
FROM evt_sponsors a, evt_sponsors b, evt_sponsors c, evt_sponsors d,
evt_details e
WHERE e.evt_detail_date <= DATE_SUB(NOW(), INTERVAL 1 DAY) AND
e.evt_sponsor_id1=a.evt_sponsor_id AND
      (e.evt_sponsor_id2=b.evt_sponsor_id OR e.evt_sponsor_id2=0) AND
(e.evt_sponsor_id3
       =c.evt_sponsor_id OR e.evt_sponsor_id3=0) AND
(e.evt_sponsor_id4=d.evt_sponsor_id OR e.evt_sponsor_id4 =0)


Thanks for the help, Erik W. Meyer


========================================================
CaptionKit http://www.captionkit.com : Production tools
for accessible subtitled internet media, transcripts
and searchable video. Supports Real Player, Quicktime
and Windows Media Player.

VideoChat with friends online, get Freshly Toasted every
day at http://www.fresh-toast.net : NetMeeting solutions
for a connected world.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux