How to populate third table with foreign keys from other two tables

[thumoo_main]

Hi all,

I have experience of using a single mySQL table, but now want to try using multiple tables. I have been reading articles and researching for weeks, but still don’t understand part of it.

I have three tables as follows:

USERS
id (PK)
username
password

PICTURES
id (PK)
filename

LINKS
id (PK)
user_id (FK)
picture_id (FK)

I can, with the relevant code, fill out the USERS and PICTURES table, but I don’t know how the LINKS table is supposed to be populated. Do I have to populate this myself by calling the id from each of the first two tables? This seems like a very convoluted way of doing it (but it may be right). Or does it auto-populate because the foreign keys are there (something that I’m hopeful about, but doubt).

[m_hutley]

The table won’t ‘auto-populate’… because it can’t assume you WANT to join every row in the first table with every row in the second.

Describe the relationship between Users and Pictures for me.

Standard relationship form comes in a bidirectional reference sentence:
A User can have ____ Picture(s); a Picture can belong to ____ User(s)

Where the blanks are usually filled in with “one”,“many”, “zero-or-one” or “zero-to-many”.

[thumoo_main]

Ah, I didn’t think about putting down the relationships.

A user can have many pictures, but a picture can belong to only one person. So a one-to-many (or many-to-one).

[m_hutley]

Okay, so in the case of a 1 to many relationship, you don’t actually need a third table.

Consider this instead:
USERS
id (PK)
username
password

PICTURES
id (PK)
user_id (FK)
filename

[thumoo_main]

That makes things a bit simpler. So with these two tables, I would need to fill in the USERS table first, otherwise the PICTURES table couldn’t be populated (as there would be no FK to reference). So I would:

1. Populate the USERS table.
2. Retrieve the user_id.
3. Populate the PICTURES table.

[m_hutley]

and i’m sure Rudy will be along momentarily to tell me I shouldnt use an artificial key in the users table.

Yes, you would need to make the user before you make the picture(s) associated for that user - which, really when you think about it, makes sense - you wouldn’t define a bunch of pictures before you knew what user the pictures were for - s’just wasteful.

[thumoo_main]

It does make sense completely. A picture wouldn’t be helpful without knowing who it belonged to.

[r937]

sorry for the delay getting here… was asleep (i’m in Toronto)

artificial key here is warranted… users do occasionally want to change their usernames

without the artificial id (using username as PK), you’d have to declare ON UPDATE CASCADE for the FK from the pictures table

[thumoo_main]

Thanks for the help so far.

So user_id in the pictures table is the artificial key? How would that change if usernames were made not to be changed? I’m happy with the way it is. Giving the ability to change usernames is fairly standard, but it’s just so I can understand it better.

[m_hutley]

No.

When we say an artificial key, what we mean is the id field in your tables.

As opposed to a Natural Key.

A Natural Key uniquely identifies a row of the table just using the data fields of that table.

So, for example, take the Users table. You could say that there is a Natural Key in the table, if username is unique across the entire table. A forum, for example, has unique usernames across it’s user table - there can be no other “hutley_m” in the table. So, you could use username as a natural key.

An artificial key is introducing another value to your table, expressly for the purposes of making a unique identification method.

There is also the idea of ‘simple’ or ‘compound’ keys - which is basically saying whether the key is made up of a single field (simple) or multiple (compound), usually indicated as a tuple.

For example - uhhhhh… okay
Courses in college.
A course identifier - CSC101, is not unique by itself - multiple teachers will teach it, at multiple times of the day/term… BUT… a teacher cannot be teaching multiple courses of CSC101 at the same time of day in the same term. So there exists a compound natural key - (courseid,teacherid,daytime,year).

The trick to these keys comes when you start linking tables together - it’s rather space consuming to import 4 columns into your other table in order to identify the unique row reference. Which is when an artificial key becomes useful/relevant.

Arguably, in your layout, username could be a natural key, that could be used as your FK in pictures. But that’s for you to determine.

[thumoo_main]

That’s really useful information. Thanks! The more I understand, the more likely I am to pick this stuff up. I’m currently working on the two databases mentioned so I’ll see what happens