relational database primer?

[dpopiz]

I still don't really understand the concept of relations in databases, but I'm pretty sure it's what I need to do for a project I'm doing with mysql.

Can any of you explain basically how it works? I'm having trouble finding any sort of tutorial/primer about relations on google.

Basically what I want to do is have a bunch of different tables where some of the fields are "linked" because they are actually the same field and should contain the same data. And then I'm hoping that in php, I can write some sort of SQL query that will be able to select all the fields related to a particular row, even if they aren't all in the same table.

 

[kamper]

Relations are essentially just foreign keys.

If A and B have a one-to-one relationship then they should probably be in the same table, but may also be done in seperate tables using a unique foreign key in one of the tables.

If A and B have a many-to-one relationship (many A's can belong to one B) then A should have a foreign key to B.

If A and B have a many-to-many relationship then you have to use a map table. Essentially just a two column table that has a foreign key to each of A and B.

Foreign keys pretty much always reference the primary key of the other table.

Is that more or less what you're looking for? Selecting across tables requires the use of joins, which can be a bit tricky at first...

 

[dpopiz]

ok, sounds simple enough, now how do I do that in MYSQL? it's looking like mysql might not even support that sort of thing

 

[kamper]

Oh, it most definitely does It couldn't call itself a relational database if it didn't.

The part that I explained above is set up when you create your tables, which is a one-time thing (at least per install). Have you read the syntax of CREATE TABLE? The next part will be doing your inserts and selects and stuff and that would be running on a regular basis from your php.

Here's the CREATE TABLE spec, but it looks like a better reference than a tutorial. Here's the main manual page. Chapter 3 looks like it may help some (didn't check too thoroughly).