Sql-server – Database Design for Paper Submission System

database-designdatabase-recommendationrelational-theorysql server

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.

Best Answer

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

Related Question