[PHPwestoz] [OT] Relational SQL Databases

[Evan Barter]

Timothy White wrote:

> Once again. Another MySQL question that is related to a PHP project. I
> have tried googling but can't find anything simple.
>
> Basically I need to have a table of songs and I want to keep the artists
> in another table as well as albums.
>
> So it will look a bit like this.
>
> Music.Artists
> ID   |   Artist
> 1    |   Avril
> 2    |   Baxtar
> 3    |   Zebra
> ...
> Music.Albums
> ID   |   Album   | Artist
> 1    |   Pink    | Baxtar
> 2    |   Mouse   | Baxtar
> 3    |   Rock    | Zebra
> Music.Songs
> (Same as before)
> CREATE TABLE library (
>
> ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
> Filename TEXT NOT NULL,
> Artist TEXT NULL,
> Title TEXT NULL,
> Album TEXT NULL,
> FID VARCHAR(40) NOT NULL,
> PRIMARY KEY ID (ID),
> INDEX UID (FID(40))
> ) TYPE = innoDB COMMENT ='';
>
> Now I know that I need them to be of type innoDB for relations to work.
> How do I work with a database like this.
> e.g. How do insert a new song and if the Artist isn't in the Artist
> table it adds it and if the album isn't in the Album table it adds it as
> well.
>
> Also how do the lookups work?
> e.g. If I do 'SELECT * from Songs WHERE Artist="Avril"' then how does it
> return the Album field? As a ID to the Album in the Album table or as
> the Album name?
>
> Thanks
>
> Tim
> -- 
>
> Tim White - Use the Fox, Luke!
> PGP/GPG id: 602E944D, Pub Key Serv: subkeys.pgp.net
> Fingerprint: 04C2 9682 B7B2 3006 009D  A9F3 067E EDCD 602E 944D
> Hi! I'm a .signature virus! Copy me into your ~/.signature to help me 
> spread!
> -- 
>
Hi Tim,

(Scrap everything I'd just typed out regarding normalized data and 
foreign keys, Nicolas beat me to it.)

Here's the thing. I wouldn't use InnoDB. You really don't need the 
features it has to offer over MyISAM (ACID, transactions, whatnot) and 
for simple selects, MyISAM is faster. (More scrapping.)

Regards,
Evan