Re: Uniqueness, NULLs and design options

Is the "main" attribute an attribute of the relationship you are representing in the article_authors table? If so it should be added to article_authors. Your suggestion that NULL be used to indicate that an author is not a main author is not a wise use of NULL. NULL in this column should mean that you're not sure whether the author is a main author or not because your information is incomplete.

May I suggest that your "main" attribute to include an allowed value that indicates the author is not a main author of the work in question. One possibility is to use a 'Y'/'N' character flag. You could also establish an author order using 1,2,3,...etc. with 1 being the main author. You could then partially enforce the "only one" part of your "one and only one" constraint by setting up a unique index on all three columns of article_authors.

Even with the index, you would still have the possibility that there would be no main author assigned. The only way to enforce this simply (without triggers) that I can think of is to add the foreign key to the article table and prohibit NULLs. This would be a legitimate move from an entity/relationship standpoint if the main author relationship were qualitatively different that the other author relationships. If this is the case, then I don't think you need an article_authors record for main authors, which means that this is not a redundant relationship.

"ks" <usenet_spam_at_online.no> wrote in message news:3b1103ed.22595851_at_news.online.no...
> 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.
Received on Sun Jul 22 2001 - 01:27:33 CEST