SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot toma's Avatar
    Join Date
    Jul 2001
    Location
    Tempe, AZ
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database Structure Question

    I understand that it is a good practice when storing data in a database to separate different kinds of information into separate tables. I notice in Kevin Yank's article on database driven websites, however, that closely related information is stored in a single table. So I'm wondering what the best structure is for the data I'm storing about musical works and their composers.
    Here's what I have now:
    • A database called 'works'
    • A table named 'works' with fields: id, WorkTitle, WorkSource, comments, audio, cid, gid
    • A table named 'composers' with fields: id, name, country, dates, profile, pic, bookmark1, bookmark2, bookmark3,
    • A table named 'genres' with fields: id, and genre


    The cid and gid fieds in the works table are for composer id and genre id. Should some of this information be further separated into other tables? What are the implications of this structure vs. a more (or less) separate one for working with the data and presenting it on web pages?

    Thanks for thoughts

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    your structure is perfect, assuming each work has only one composer (is this a safe bet?) and furthermore that each work is in only one genre (probably too restrictive)

    take gid out of works and create a new table workgenre, with columns wid and gid as foreign keys to works and genres tables respectively, and make the pair (wid and gid) the primary key, thus ensuring that each work is in any genre only once

    workgenre is thus the "intersection" table that implements the many-to-many relationship between works and genres

    do the same with composers if you want to properly record works by lennon & mccartney, etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot toma's Avatar
    Join Date
    Jul 2001
    Location
    Tempe, AZ
    Posts
    163
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    assuming each work has only one composer (is this a safe bet?
    Yes it is. Thanks for the reply.
    take gid out of works and create a new table workgenre, with columns wid and gid as foreign keys to works and genres tables respectively, and make the pair (wid and gid) the primary key
    Do I make wid and gid foreign keys by simply adding a wid column to works and gid column to genres after creating the new workgenre table?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Do I make wid and gid foreign keys by simply adding a wid column to works and gid column to genres after creating the new workgenre table?
    no, wid is a foreign key to id in works, and gid is a foreign key to id in genres



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
  •