How would you design Books database with Ghost Writers or House Names?

[PaulAlex7000]

I have this Access database comprised of books.

Authors and Titles tables, with an author/title linking table in
between. All good.

As it currently stands, the authors of the books are the actual
writers -- or ghost -- of the books.

I am thinking of adding a House Author field.

For example, one of those Tom Clancy Net Force books. The author name
would be Steve Perry, but the House Author Name would be Tom Clancy.

How would you set up a linking table for this?

Would you set up a linking table between Actual_Authors and
HouseName_Authors? Because:

1) A House Name -- Tom Clancy -- can have many ghost writers, and

2) Many ghost writers can write under the Tom Clancy name.


Or would you actually want to set up a linking table between the Title
and Actual_Authors? Because:

1) A House Name can write many different series (e.g., Net Force,
Covert Ops, Power Plays). Series is a field in my Titles database, and

2) A Series could theoretically change its House Name. (Only done once
that I know of.)

So the Titles table would point to two linking tables -- Authors, as
well as with Actual_Authors.


How would you plan this?

 

[John Nurick]

Hi PaulAlex,

I'd probably add a field to the AuthorsTitles table to store the
relationship between the "author" and the book:

Books
BookID, Title
1234, "Another One From the Clancy Factory"

Authors
AuthorID, AuthorName
999, "Clancy, Tom"
1000, "Perry, Steve"

AuthorshipTypes
Author
Ghost writer
House author
Contributor
Employed writer

AuthorsTitles
BookID, AuthorID, AuthorshipType
1234, 999, House author
1234, 1000, Author

 

[Fred Boer]

Dear PaulAlex7000:

I'm just curious, have you done anything about multiple authors? One option
is yet another field listing if the author is the primary author or not.
This becomes an issue in creating an identifying code for shelving...

HTH
Fred Boer

 

[PaulAlex7000]

Thanks John.

I see what you mean -- making a composite key out of three primary
keys. I like your idea.

 

[PaulAlex7000]

Yeah, my database has fields for up to 4 multiple authors).

But for regards to identifying code for shelving, that's why I want to
create a HouseAuthor field. So it's easier to sort and whatnot.

And for those books that have authors but there is no House Author (in
other words, a "regular" book and not some serialized pulp fiction
book), then the I'll propogate the Author info to the HouseAuthor
field..