Re: Database Design Issue

[Jason Lange <jslange@myrealbox.com>]

Yes, songs do span shows. However, the list of songs for each weeks show starts out as a *.asc (same a *.txt) space-separated file. This gets uploaded via a PHP file that formats each row and inserts it into the database. That's why even though I know there will be duplication it appears (to me at least) I'll just have to live with it. I have no guarentee that the songs will appear in the *exact same way* every time either (you really don't want to know why -- I'm getting a migrain just thinking about it). I've attached an example of the incoming files I have to deal with for your reference.

Also, I'm just designing the website I will have no part in the everyday maintenance of the website -- except when the maintainer gets stuck.  :)

Jason

CPT John W. Holmes wrote:

From: "Jason Lange" <jslange@myrealbox.com>

  

Here's my question. I'm creating a website for a weekly radio show. One
of the items I would like to store in the database is the list of songs
played in each show (there's normally between 20-25 songs per show), and
the songs will be displayed by show. Should I put all of the songs in a
single table with a field denoting what show they're from, or should I
create a separate table for each weeks show and just request that entire
table? I'm looking at this from both a long-term performance and
*maintenance* issue.
    

The first question is if songs can span multiple shows? If so, you need 3
tables. One to hold the songs, each song getting a unique id. One table to
hold the shows, each show getting a unique id, and finally a third table
that relates the two. You'd list the song_id and the show_id that it's used
in. Add in a row for each song used in each show.

If a song is not shared across shows, then you could go with two tables. A
show table where each show gets a unique id and a song table with a column
that has a show_id in it relating it to what show it's used in.

You'd be better off with the first method, though, because sooner or later
down the line there's going to be a song shared, I imagine. :)

---John Holmes...


  

FOREVER WE WILL SING         MICHAEL W. SMITH            WORSHIP *DVD*               (REUNION   )
YOUR LOVE OH LORD            THIRD DAY                   OFFERINGS: A WORSHIP ALBUM  (ESSENTIAL )
ENOUGH                       CHRIS TOMLIN                OUR LOVE IS LOUD            (SPARROW   )
LORD YOU'VE BEEN GOOD TO ME  GRAHAM KENDRICK             WHAT GRACE                  (FURIOUS   )
LET THE RIVER FLOW           DARRELL EVANS               LET THE RIVER FLOW          (INTEGRITY )
MY GOD REIGNS                DARRELL EVANS               LET THE RIVER FLOW          (INTEGRITY )
I LAY ME DOWN                DARRELL EVANS               FREEDOM                     (VERTICAL  )
I AM IN LOVE WITH YOU        DARRELL EVANS               FREEDOM                     (VERTICAL  )
BREATH OF GOD                CHRISTY NOCKELS             GIRLS OF GRACE              (WORD      )
SPOKEN FOR                   MERCY ME                    SPOKEN FOR                  (INOTOF    )
PRAISES BE                   LINCOLN BREWSTER            AMAZED                      (VERTICAL  )
LORD OVER ALL                SONIC FLOOD                 RESONATE                    (INO       )
HUNGRY                       KATHRYN SCOTT               HUNGRY                      (VINEYARD  )
BE GLORIFIED                 TIM HUGHES                  ALL AROUND THE WORLD        (WORSHIP T )
LORD YOU HAVE MY HEART       DELIRIOUS                   CUTTING EDGE                (SPARROW   )
TRADING MY SORROWS           DARRELL EVANS               FREEDOM                     (INTEGRITY )
FIELDS OF GRACE              DARRELL EVANS               ALL I WANT IS YOU           (VERTICAL  )
ALL I WANT IS YOU            DARRELL EVANS               ALL I WANT IS YOU           (VERTICAL  )
WE WILL EMBRACE YOUR MOVE    DARRELL EVANS               LET THE RIVER FLOW          (INTEGRITY )
LORD REIGN IN ME             VINEYARD U.K.               WINDS OF WORSHIP 12         (VINEYARD  )
YOU SHINE                    BRIAN DOERKSEN              YOU SHINE                   (INTEGRITY )
GOD OF WONDERS               CAEDMON'S CALL              IN THE COMPANY OF ANGELS    (ESSENTIAL )

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