Help with primary keys and foreign keys

yoki

Hello,

I'm trying to figure out where I should be applying my primary keys and foreign keys on my tables.

I have two tables: (Primary keys are underlined)

Here is table 1:

Abstract

Abstract_ID B[/B]
Title
Date_Received
Author_ID

Here is table 2:

Authors

Author_ID B[/B]
Abstract_ID (FK)
Last_Name
First_Name
E_Mail

I'm wondering if I need the Abstract_ID in table 2 since each abstract will have one presenting author and maybe other co-authors linked to it.

Can someone help me, I'm mixed up.

laserlight

The relationship between abstract and presenting author is many-one.
Your abstracts table should have a presenting author id column as a foreign key.

The relationship between abstract and co-authors is many-many.
Your should have another table to store the relationships between abstracts and co-authors.
Your abstracts table should have a foreign key, which would be the id of this new table.

yoki

Thanks for your reply! 🙂

So my database becomes this? Right? Do I have to insert Abstract_ID as a foreign key in the co-authors table though?

Abstract

Abstract_ID (PK)
Title
Date_Received
Presenting_Author_ID (FK)
Co_Author_ID

Presenting_Authors

Presenting_Author_ID (PK)
Last_Name
First_Name
E_Mail

Co_Authors

Co_Author_ID (PK)
Last_Name
First_Name
E_Mail

yoki

I have another question...

How can I query my database to search for co-authors that are linked to this abstract?