Three tables; articles, authors and the intermediary table
article_authors linking the two first in a many-to-many relationship.
Now, an article may have several authors. BUT only one "main" author
who gets most of the credit. And the articles are ordered
alphabetically by this authors name in the physical archives.
Now, should I add another column to the article table - pointing
directly to the one main author. That means two parallel relationships
between articles and authors.
Or, is there a way of enforcing one single "main" author in the
intermediary table by adding a column ? The table would then have
author_id, article_id and main. Most rows would have a NULL, but for
each article there must be ONE author flagged as "main" - no more, no
less.