0

I am learner of Database design, I want to design a database for a journal paper submission system in which one paper can be submitted by one or more author. I have initially two tables Author and Paper

Table Author has attributes like,

AuthorID
Name
Contact
Email
URL

Table Paper has attributes like

PaperID
Title
Abstract
Keywords

I need a third table submission where i can preserve association between paper and authors/author. Can any one help me in this regard to find possible association.

2

If all you want to do is keep track of which authors contributed to which papers, then all you need is a simple intersecting entity (see more) like this:

ERD1

Here the columns of the SUBMISSION table are just the two primary key columns from PAPER and AUTHOR. These two columns will jointly form the primary key of the intersecting table and will also each individually be foreign keys to their respective tables.

NOTE: Don't add any other columns to the SUBMISSION table, unless those columns can reasonably be expected to vary row by row for the same paper and for the same author. For example, it would be a bad idea to add a date to the submission table unless a single paper is submitted at various times by multiple authors. If you need to track information about the event of an author or group of authors submitting a paper, then you need to normalize out the extra information to avoid creating update anomalies. Consider the following:

ERD2

In the above ERD, notice how the extra information that is not author-dependent remains at the SUBMISSION level, while the new intersecting entity CONTRIBUTION is added to record the connections between authors and a particular instance of the submission of a paper.

This four table model is more robust because it allows for papers to be resubmitted multiple times with changes in authorship each time. If this kind of real world nuance is important to your system then you would be better off with the four table approach than with a three table approach.

Here is the four table model with more column details at OP's request:

Detailed ERD

  • Thank you so much Joel, you solved my 75% problem, I was confused in selection of foreign and primary keys your explanation helped me come out of that confusion. Thanks mate – Irfan Ali Memon Nov 5 '14 at 6:39
  • Sir @Joel I understood ERD with 3 tables including Paper, Author, and Submission. But ERD with 4 tables is confusing me can you help plz to design the database with your suggested 4 tables ERD. – Irfan Ali Memon Nov 5 '14 at 10:27
  • @IrfanAliMemon In the ERD with four tables, SUBMISSION records the act of a paper being submitted. Assuming the editing process may sometimes involve rejection(s) and resubmission(s) this is 1:M with PAPER. For each such act of submitting a paper, there may be a different list of AUTHORs - which is M:N with SUBMISSION. This M:N relationship is resolved with the CONTRIBUTION table. – Joel Brown Nov 5 '14 at 11:59
  • Sir @JoelBrown what could be possible columns in Contribution Table, What should be the primary, foreign keys for this table, also how can I ensure relation between New and Revised Submision. – Irfan Ali Memon Nov 12 '14 at 7:59
  • @IrfanAliMemon The Contribution table is a pure intersection. It would have a primary key that is composed of the combination of the two foreign keys to Submission and Author. There is no need for any other columns in the intersection table. New and revised submissions are implicitly related insofar as they are for the same Paper and therefore share the same foreign key value, e.g. PaperID. – Joel Brown Nov 12 '14 at 13:36
0

As there can be multiple authors for a single paper, so You can add one more table Submission where you can have attribute as

PaperID -- FK (PK of table Paper)
AuthorID -- FK(PK of table Author)
SubmissionDate - DateTime2

Rest of your design looks good. Now you can create Unique Constraints on columns PaperID and AuthorID in table Submission

ADD CONSTRAINT UniqueSubmission UNIQUE (PaperID , AuthorID);
  • Thanks Vijay, I have also submission table in my design but I was confused in selection of primary key for that table your prompt response helped me solve this. – Irfan Ali Memon Nov 5 '14 at 6:56
0

enter image description here

@

This is the possible solution I have made after getting valuable feedback from Sir Joel Brown and Vijay. Kindly verify, if it is valid.

  • You have misapprehended, somewhat, the intention of the CONTRIBUTION table and my repurposing of your SUBMISSION table. Please see my edited answer for clarity. – Joel Brown Nov 13 '14 at 12:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.