Re: General design question about many-to-many relationships

brendalizallen_at_yahoo.com (Brenda) wrote in message news:<a72e7d96.0110090653.4245965f_at_posting.google.com>...
>
> I am putting together a design for a database to support a
> publishing-related web site. The objects include articles, publishers,
> writers, content providers, and funders. All objects are of equal
> interest to the users. In other words, users will be as interested in
> finding articles as they will publishers or content providers. And
> what is important is that a user can, for example, find all the
> articles written by a particlar writer or all the articles published
> by a particular publisher. And writers can write for content providers
> or simply write an article, or both.
>
> So I have the following so far:
>
> TABLES:
> article, publisher, writer, contentProvider, funder
> article_publisher, article_writer, article_contentProvider, article_funder
> writer_contentProvider, writer_publisher etc

I.e. one group of tables representing entities, a second group representing links between entities

> Being new to a project with so many m-to-m relationships, do database
> designs often involve numerous joining tables to provide the kind of
> functionality needed in my case?
>
> For example, if a user liss all articles then they should see links to
> the publishers, writers, content providers, and funders associated
> with that article. Then, if they select, for example, a writer they
> should see all the articles, publishers, content providers and funders
> with which they are associated.
>
> My first thought was to generally relate all items to the article. But
> really all entities are equally stand-alone.
>
> Thank you for any general thoughts.
>
> Brenda

Your design is correct.
However, with five entities there are already ten link-tables. Imagine some more entities (editor, publication, graphic artist?) and it becomes clear that this design is not very maintenance-friendly. The number of link-tables grows at a much faster pace than the number of entities. Also, if someone is both a writer and an editor, you are going to store redundant data.

I would try to reduce the number of entities. Suppose that for all persons who are associated with an article you collect essentially the same information. Then there could be one table 'person', instead of four (publisher, writer, contentProvider, funder)

There must be an n:m link between person and article, 'person_article'. This is insufficient, because users are also interested in the role a person plays regarding an article. If person_article is allowed to carry this additional information, it becomes a 3-way link person_article_role. I choose to call this relation 'Involvement'

We have now a partial design:           Role
                                         |
                         Article --- Involvement --- Person
Sample contents:
ROLE       ARTICLE         PERSON         INVOLVEMENT
Writer     Intro to DBMS   Chris Date     Chris Date, Writer, Intro to

Maybe publishers are companies instead of persons, but that is just a minor detail. Nearly all useful information can be gathered via Involvement. I guess it is better not to store actual data in this table, but things like ArticleID, RoleID and PersonID.

If persons can be associated to one another other than through their common involvement with one or more articles, another link-table is needed between Person and Person. I have a feeling that this is not the case. Received on Fri Oct 12 2001 - 16:27:38 CEST