Relating authors real names to pen names in database

I'm kicking around a small database project (sqlite or mysql) for learning purpose开发者_开发百科s... figured I'd work on cataloging some of my many books ;)

Thought I had most of the tables and relationships worked out, until I started going thru and populating with some sample data from a particular book series. One of the authors writes under a pen name in this series/genre, but under a completely different name for another genre, and his 'actual' name is something else entirely. Add to the fun that sometimes the reader (me) may not be aware that the authors name on the cover is a pen name or not.

Any ideas or suggestions for how to deal with this sort of thing in practice?

TIA,

Monte

---

The answers from @Adam and @mellamokb do not take account of some other complications:

1. Pen names can be used by authors
2. An author may have zero-many pen names
3. A pen name can belong to an author
4. A pen name can be a house (publisher) pen name, used by zero-many authors
5. A pen name is often adopted, when an author starts collaborating with one or more other authors, for the collaboration.
6. As you say, it is not always obvious that a name is a pen name, so you have to make the assumption that a name is real unless you know otherwise.

Obviously, dealing with all these cases is only worth while if the number of books is large - before I disposed of my book collection (approx 15,000) I was contemplating making a shift to this sort of solution, because of pen names, and because of edited anthologies and article series.

If you wish to go down this route, a good place to start is Martin Fowler's Analysis Patterns or the IEEE standards for author descriptions for its own publications.

---

One of many possible solutions:

• tblAuthors contains authors (only real names).
• tblPenNames contains pen names and relates to tblAuthors.
• tblBooks has an AuthorID linking to tblAuthors and an optional PenNameID linking to tblPenNames.

Another solution

• tblAuthors contains authors or pen names.
• tblAuthors has an optional PenNameForAuthorID linking to another author.
• tblBooks has an AuthorID linking to tblAuthors.

---

I would create three name-related columns in the authors table, e.g.:

authors
-------
author_id
author_name
author_alt_name
author_pseudonym

and presuming the books table has a foreign key to authors, e.g.:

books
-----
book_id
book_title
...
author_id

...then you could query for books by that author using author_id and thus independent of the various names s/he uses.