Re: Relationship between tables

[Sam Hocking]

Thanks for your reply claude

I guess because i'm moving over from M$ Access i'm used to setting up relationships betwen tables in the normaliztion and design process. i.e if 2 tables have a relationship setup between their ID's (primary keys) and i insert data in one table, the data is also inserted (available for selecting) in the related table through this relationship connection i've set. In mysql i have to insert duplicate data into each table i want to be related it seems. ie physically put matching ID columns with the same ID value in both tables. Is this correct?

I think the main thing i need to get my head around is to view mysql as being more static and flat and that its power is from using the sql rather than the actual database / any relational feature.

Anyway, so far I've got about 10 tables in all. On three tables at the moment i'm doing this as a test e.g:

SELECT TrackTitle, ArtistName, LabelName, RecordTitle
FROM data.tracks
LEFT JOIN data.artists ON tracks.ArtistID = artists.ID
LEFT JOIN data.labels ON tracks.LabelID = labels.ID
LEFT JOIN data.records ON tracks.RecordID = records.ID
WHERE ArtistName = $form{'artistName'}

I'm in the process of converting a musician friend of mines 4 record labels (about 1,000 releases (4,000 odd tracks) spread accross 5 different record labels) over from m$ access to mysql. I didn't want to just convert access to mysql using a conversion program as i want to learn mysql and use it instead. I've only been using mysql for a couple of days and managed to hook it up to perl yesterday to start doing some cgi with it. The above is one of my first selects with left join.

Does the above look right? ie i just give every table a tablename.ID column and wherever i want it related to another table set up a tabelnameID column that has the same values from the first table in it?

Thanks for all your help Claude.

Sam