[SOLVED] Help creating database structure

[anonova]

Hi, I am trying to create a database structure, but I cannot think of a way for it to work. Below is what I have currently.

 

I am trying to create a database to manage song lyrics. For people, I use

SELECT id, personname FROM people

This way on the next page, I can use this to get the name of the CD and artist.

SELECT personname, disconame FROM people AS p, disco AS d WHERE p.id = d.personid AND p.id = {$id}

The problem with this is that I do not know how to link the tracks to the lyrics page. The tracks column in disco is just set as a text field seperated by new lines.

Don't Be Afraid
Blue Fields
Oath

 

I am looking for a way to link the tracks to the lryics page or create a relationship between disco and lyrics. What is a possible way of doing this? My server is running PHP 5.2.0 and MySQL 4.1.22.

CREATE TABLE people (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  personname VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE disco (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  personid INT(10) NOT NULL,
  disconame VARCHAR(255) NOT NULL,
  tracks TEXT NOT NULL,
  release DATE NOT NULL,
  asin TEXT NOT NULL,
  PRIMARY KEY (id)
);
CREATE TABLE songs (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  songname VARCHAR(255) NOT NULL,
  personid INT(10) NOT NULL,
  lyrics LONGTEXT NOT NULL,
  notes TEXT NOT NULL,
  art TEXT NOT NULL,
  PRIMARY KEY (id)
);

[bubblegum.anarchy]

Consider something like this (pretty much the same but with disc_id added to track):

 

disc.id

disc.artist_id

disc.name

disc.release

disc.asin

 

track.id

track.disc_id

track.artist_id

track.name

track.lyrics

 

artist.id

artist.name

 

No need for disc.tracks since each track would be associated via track.disc_id

[Illusion]

If every table has the primary key then what about the foreign key.

[anonova]

Consider something like this (pretty much the same but with disc_id added to track):

This sounds excellent. Thank you, bubblegum.anarchy!

If every table has the primary key then what about the foreign key.

I've never used foreign keys so I'm not sure how they work. I have set them like

FOREIGN KEY (disco_id) REFERENCES disco(id)

 

Do foreign keys have any affect on the queries I will be using?

[Illusion]

Do foreign keys have any affect on the queries I will be using?

Obviously they will have effect when you use UPDATE and INSERT statements

[anonova]

Ah, okay. Thanks for the help.