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)
);