Database headache

Dev Shed Forums Sponsor:

You don't need a fax machine to get faxes. Get a fax-to-email fax number from CallWave. Try it free.

May 12th, 2003, 11:17 AM

scipione

Junior Member

Join Date: May 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

Database headache

Hello everyone,

I'm trying to make a classical music CD database in MySQL, but I'm having some trouble, I'd appreciate some help

1. I can't figure out how to make the track titles database for the CDs! Should I use a table for each CD? I'd end with a lot of tables...

2. I made a "composers" table, so the CD table links to it for composers' info, but what can I do if a CD contains works by more than one composer? I did the same with the "performers", but again I don't know how to link one CD to more than one performer...

I apologise if this is too dumb a question, but I can't figure the answers out. Thanks for your help.

May 12th, 2003, 12:00 PM

Mirax

Senior Member

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Jun 2000

Location: Enschede, The Netherlands

Posts: 1,527

Time spent in forums: 2 h 49 m 2 sec
Reputation Power: 9

Hi scipione, welcome to these forums!

There are probably several solutions for this but I'd go for something like

Code:

albums
-------------------------
albumID
albumName
albumYear
albumWhatever....

tracks
-------------------------
trackID
trackAlbum (link to albumID)
trackNumber (if you want tracks on album to be ordered)
trackName
trackLength (if you want)
trackComposer (link to compID)
trackPerformer (link to perfID)
// the last 2 are to be able to set this for each track in case of a compilation cd

performers
-------------------------
perfID
perfName
perfWebsite
perfWhatever

composers
-------------------------
compID
compName
compBiography
compWhatever

If people have some improvements/alternatives let it hear

__________________
There are 10 types of people in this world - those who understand binary and those who don't...

PHP | MySQL | DevShed Forum Search | Google Search

May 12th, 2003, 12:38 PM

scipione

Junior Member

Join Date: May 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

Thank you Mirax! I'm trying it soon

May 12th, 2003, 10:26 PM

scipione

Junior Member

Join Date: May 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

I followed the advice and re-organised the database, and it turned out to be VERY complex, I think... solved some problems but found new ones. Would someone be kind enough as to review the database structure and give me some more advice? I'd be grateful

Code:

TABLE "albums"
---------------------------
albumID (the ID number for the album)
title (album title)
labelID (ID of the recording label)
year (year of release)
code (record's code)
setID (ID of the album set type, eg: 3 CDs, 1 CD)

TABLE "sets"
---------------------------
setID (ID number for set)
settype (set type, eg: 1 CD, 3 CDs)

TABLE "composers"
---------------------------
composerID (ID number for composer)
name (composer's first name)
surname (composer's last name)
bio (composer's biography)
dates (birth-death dates)

TABLE "labels"
---------------------------
labelID (ID number for label)
name (recording label name)

TABLE "orchestras"
---------------------------
orchID (ID number for orchestra)
name (orchestra name)

TABLE "performers"
---------------------------
perfID (ID number for performer)
name (performer's first name)
surname (performer's last name)
positID (ID number for performer's position, eg: conductor, piano)
bio (performer's biography)
dates (birth-death dates)

TABLE "positions"
---------------------------
positID (ID number for position)
posittype (position, eg: conductor, piano)

TABLE "works"
---------------------------
workID (ID number for work)
worktitle (work title, eg: Trumpet Concerto in D)

TABLE "tracks"
---------------------------
trackID (ID number for track)
albumID (which album the track belongs to)
number (track number on the CD)
name (track name)
workID (to which work the track belongs)
lenght (duration of the track)
compID (composer of the track)
orchID (orchestra playing the track)
perfID (performer on the track)
cdID (which CD the track is on, for multiple CD sets)

The problem I spot now is that I can't assign more than one performer to a track (eg: Eugen Jochum, conductor; Kiri Te Kanawa, soprano; Stuart Burrows, tenor; and so on). Any help for that?

Thanks again!

May 16th, 2003, 12:04 AM

merl

Contributing User

Join Date: Aug 2002

Posts: 51

Time spent in forums: 5 h 6 m 34 sec
Reputation Power: 6

Putting the ID from one table into another table is a fine way of implementing a one-to-many relationship. You seem to have that down pat in your design so far

If each performer can appear on only one track, but each track can have many performers, the solution to this is to have the trackID in the performer table, like so:

Code:

TABLE "performers"
---------------------------
perfID (ID number for performer)
trackID (ID number for track)
name (performer's first name)
surname (performer's last name)
positID (ID number for performer's position, eg: conductor, piano)
bio (performer's biography)
dates (birth-death dates)

TABLE "tracks"
---------------------------
trackID (ID number for track)
albumID (which album the track belongs to)
number (track number on the CD)
name (track name)
workID (to which work the track belongs)
lenght (duration of the track)
compID (composer of the track)
orchID (orchestra playing the track)
cdID (which CD the track is on, for multiple CD sets)

But if each performer can be on multiple tracks then you need a new table to represent this many-to-many relationship.

e.g.

Code:

TABLE "track_performers"
---------------------------
perfID (ID number for performer)
trackID (ID number for track)
positID (ID number for performer's position, eg: conductor, piano)

TABLE "performers"
---------------------------
perfID (ID number for performer)
name (performer's first name)
surname (performer's last name)
bio (performer's biography)
dates (birth-death dates)

TABLE "tracks"
---------------------------
trackID (ID number for track)
albumID (which album the track belongs to)
number (track number on the CD)
name (track name)
workID (to which work the track belongs)
lenght (duration of the track)
compID (composer of the track)
orchID (orchestra playing the track)
cdID (which CD the track is on, for multiple CD sets)

I've taken the liberty of removing position from the performers table, as it might be possible for a performer to have different positions on different tracks. If that's incorrect, then by all means move it back to the performers table.

Does that help?

May 18th, 2003, 02:35 AM

scipione

Junior Member

Join Date: May 2003

Posts: 6

Time spent in forums: < 1 sec
Reputation Power: 0

Thanks for the answer, merl!

I think I might finally have it, this is the latest shape of the database:

Code:

TABLE "albums"
---------------------------
albumID (the ID number for the album)
title (album title)
labelID (ID of the recording label)
year (year of release)
code (record's code)
setID (ID of the album set type, eg: 3 CDs, 1 CD)

TABLE "sets"
---------------------------
setID (ID number for set)
settype (set type, eg: 1 CD, 3 CDs)

TABLE "composers"
---------------------------
compID (ID number for composer)
name (composer's first name)
surname (composer's last name)
bio (composer's biography)
dates (birth-death dates)

TABLE "labels"
---------------------------
labelID (ID number for label)
name (recording label name)

TABLE "ensembles"
---------------------------
ensID (ID number for ensemble)
name (ensemble name)

TABLE "performers"
---------------------------
perfID (ID number for performer)
name (performer's first name)
surname (performer's last name)
bio (performer's biography)
dates (birth-death dates)

TABLE "positions"
---------------------------
positID (ID number for position)
posittype (position, eg: conductor, piano)

TABLE "works"
---------------------------
workID (ID number for work)
worktitle (work title, eg: Trumpet Concerto in D)
compID (who composed the work)

TABLE "tracks"
---------------------------
trackID (ID number for track)
albumID (which album the track belongs to)
number (track number on the CD)
name (track name)
workID (which work the track belongs to)
lenght (duration of the track)
cdID (which CD the track is on, for multiple CD sets)

TABLE "performances"
---------------------------
albumID (ID number for album)
workID (ID number for work)
ensID (ID number for ensamble)
perfID (ID number for performer)
positID (ID number for performer's position)

I decided to put all the performances' data into another table and link performances to works rather than tracks; likewise, now works (instead of tracks) are linked to composers. As you pointed out, I made the position independent from the performer.

If anyone notices big mistakes in this design, please let me know! Right now, I'm wrestling with the SELECT statement in the PHP script which proccesses this database, so I think I'll have to bother you all with more questions soon (seems like I chose a rather tricky database for my first project

)...

Thank you for all the help you gave me so far

Viewing: Dev Shed Forums > Databases > Database Management > Database headache

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

Accelerating Trading Partner Performance
One in five. That's how many partner transactions have at least one error. That is an amazing statistic, particularly given the extraordinary leaps in innovation across the global supply chain during the past two decades. Download this white paper to learn more.

Competing on Analytics
This Tech Analysis is designed to help identify characteristics shared by analytics competitors, and includes information about 32 organizations that have made a commitment to quantitative, fact-based analysis.

Cost Effective Scaling with Virtualization and Coyote Point Systems
An overview of the industry trend toward virtualization, how server consolidation has increased the importance of application uptime and the steps being taken to integrate load balancing technology with virtualized servers.

Five Checkpoints to Implementing IP Telephony
Implementation planning for IP PBX software and IP telephony has become vital as businesses replace discontinued legacy PBX phone systems. This informative whitepaper outlines five "checkpoints" for any implementation plan that will help make IP communications a successful proposition.

Hosted Email Security: Staying Ahead of New Threats
In the last two years, email has become a fierce battleground between the nefarious forces of spam and malware, and the heroes of messaging protection. The spam volumes increased alarmingly every month, bringing clever new forms of phishing and virus propagation attacks.

More Free IT White Papers!