Relationships Question 1

[lumstar]

Hi there,

I'm pretty new to SQL but am learning fast. I have a database with 4 tables, Authors, Journals, Article_Details and Articles.

The Authors Table has the following columns: AuthorID (index), LastName, FirstName.

The Article_Details Table has Article_DetailsID, and then a few columns of the details (Journal, Year, Volume etc).

The Articles Table is my problem. Right now it has 2 columns: ArticleID and AuthorID.

This works fine if an article has only one author but I want to make it so that an article can have many authors. I am not sure what columns I should add and what relationships would help me with this.

Sorry for the long post and thanks in advance to anyone who has a suggestion for me.

Regards,
Lumstar

 

[DotNetGnat]

i think your table structure already takes care of that...

Table Articles can have same AuthorId but different ArticleID's...

-DNG

 

[lumstar]

So you're saying that for a single Article I should have multiple ArticleID's?

For example an article with 3 authors would 3 entries in the Articles table all with different ArticleID's.

Is the the correct way of doing things?

Thanks Again

Lumstar
______________________________________
And The Star Continues To Shine....

 

[maswien]

In your case, the Article table should have: Article_DetailsID, AuthorID, it's many to many.

 

[lumstar]

As I said, I'm new to sql, so I am going to ask a few more (maybe) silly questions. :~/

With just Article_DetailsID and AuthorID columns, I have no primary key, since there will be duplicates of both correct? How would I implement this suggestion?

Once again, sorry if these questions are annoying.

 

[DotNetGnat]

NO. I think you got me wrong there...

Lets consider this:

Authors,

AuthorId | AuthorName
_____________________

1 | James
2 | John
3 | Tom

Article_Details

ArticleId | ArticleName
_______________________

101 | Article1
102 | Article2
103 | Article3
104 | Article4

Articles

AuthorId | ArticleId
____________________

1 | 101
1 | 102
2 | 101
3 | 102
3 | 104
3 | 101

-DNG

 

[maswien]

You can add another column, say "Article_author_id", to the table and set it as primary key, if you want.

 

[DotNetGnat]

AuthorID in Author Table and ArticleId in Article_Detials table are primary keys.

AuthorId and ArticleId in Articles tables are Foreign keys..

Let us know if you have any more questions...

-DNG

 

[lumstar]

Looks good DNG. Thanks a lot!

Lumstar
______________________________________
And The Star Continues To Shine....

 

[DotNetGnat]

No Problem...post any questions if you have...this forum has loads of experts...i am learning too from them...

Thanks

-DNG

 

[lumstar]

Hate to do this, but I do have one more question. Using the following to put the data into the database leaves me with another problem:

"INSERT INTO Article_Details VALUES ('" +title+ "', '" + journalID+"', '"+year+"', '"+volume+"', '"+number+"', '"+pages+"')";
"INSERT INTO Articles VALUES ('"**What goes here**+","+authorID+"')";

How do i get the ArticleDetailsID from the ArticleDetails I just added. It is an incrementing Index.

Thanks again. And thanks for the quick responses.

Lumstar
______________________________________
And The Star Continues To Shine....

 

[DotNetGnat]

Use

@@IDENTITY to get the ID of the recently added record.


-DNG

 

[lumstar]

Amazing DNG! Thanks for all the help. One star for you

Lumstar
______________________________________
And The Star Continues To Shine....

 

[DotNetGnat]

No problem...glad that i could be of help.

-DNG