I am writing a program to help authors maintain lists of references for their research. It must be possible to reconstruct citations in various styles from the database.
In the database, I maintain a many-to-many relation between authors and the rest of the reference (title, year of publication, etc.) using a cross-reference table to link the table of authors with the reference table. Editors are treated the same way.
Being a database newbie, I would like to know the best way to maintain the order of the authors so that citations can be reconstructed correctly.
It feels like adding a column to the cross-reference is the natural way to do this -- the column would indicate :..this is the first author on that paper, this is the second..." and so on.
Is there a standard way of doing something like this?
Since database tables do not have any intrinsic order, you must represent the order yourself somehow. Adding a "counter" column to the cross-reference table indeed sounds like the intuitive (and simplest!) way of doing this.