Alex Rivera | Logout

How do you deal with m..n relationships in a relational database?

Asked 2008-10-08T16:47:01.537
18

Let's look at an example - books. A book can have 1..n authors. An author can have 1..m books. What is a good way to represent all of the authors of a book?

I came up with an idea to create a Books table and an Authors table. The Authors table has a primary AuthorID key the author's name. The Books table has a primary Book ID and metadata about the book (title, publication date, so on). However, there needs to be a way to link the books to authors and authors to books. And this is where the problem is.

Let's say we have three books by Bob. However, on one book, he wrote it as Bob, PhD. Another he wrote as Dr. Bob, and a third he wrote as Dr. Robert. I want to be able to identify the fact that these authors are, in reality, the same person but credited under different names. I also want to distinguish Bob from another Bob who wrote different books.

Now let's also add in another part to an application, a Person table that keeps track of interesting people. And let's say that Bob is an interesting person. I want to not only say that the author of all three books is Bob, but that this interesting Bob is the same Bob as the author Bob.

So what strategies exist for such potentially complicated mapping, while ensuring that the book authors are identified by the name on the cover?

sql database database-design
Edit
Report

2 Answers

0

For 1..n relationship (author has many books, author has many aliases):

  1. Put a foreign key author_id in Books pointing at author.
  2. Create a new table, author_aliases, to hold the aliases information.
  3. Put a foreign key alias_id in Books pointing at alias (nullable if author details are defaul).
  4. Put author_id foreign key in author_aliases.

If you wish, you can use intermediary tables to link authors and books, but with a 1..n mapping I don't think this is necessary.

For an n..m relationship (author has many books, book has many authors):

You would have to use an intermediary join table (author_id, alias_id, book_id) instead of foreign keys in the book table. You will want to keep the foreign key from aliases to author (for easy lookup of author aliases without having to go via all their books).

You can argue that in terms of scalability in the future this is a better way to start off as well, even if the initial specification says something is a 1..n relationship. You will find that specifications (or question) as given often are inadequate, so you will want to design in the general manner for when the specifications change or are clarified.

answered 2008-10-08T16:49:37.927
0

a possible implementation in postgresql, just for the fun of it:

create table books (
  book_id integer primary key,
  title varchar not null
);

create table aliases (
  alias_id integer primary key,
  alias varchar not null
);

create table books_aliases (
  book_id integer references books (book_id),
  alias_id integer references aliases (alias_id),
  primary key (book_id, alias_id)
);

create table authors (
  author_id integer primary key,
  author varchar not null,
  interesting boolean default false
);

create table aliases_authors (
  alias_id integer references aliases (alias_id),
  author_id integer references authors (author_id),
  primary key (alias_id, author_id)
);

create view books_aliases_authors as
  select * from books 
    natural join books_aliases
    natural join aliases
    natural join aliases_authors
    natural join authors;

one could use "using" instead of the natural join:

create view books_aliases_authors as
  select *
    from books 
    join books_aliases using (book_id) 
    join aliases using (alias_id) 
    join aliases_authors using (alias_id) 
    join authors using (author_id);

or do the complicated thing for mysql compatibility (note that mysql would also need an explicit maximal length for the varchars above):

create view books_aliases_authors as
  select b.book_id, title, l.alias_id, alias, t.author_id, author, interesting
    from books b
    join books_aliases bl on bl.book_id = b.book_id
    join aliases l on bl.alias_id = l.alias_id
    join aliases_authors lt on lt.alias_id = l.alias_id
    join authors t on t.author_id = lt.author_id;

this example doesn't use the "people" table, only an "interesting" flag to authors. please note that nothing changes (structurally), if you rename "authors" to "people"

answered 2012-07-23T09:13:41.060

Your Answer