Table design advice

[Jan Stenqvist]

Hello,

I'm trying to figure out the best table design for a book database I'm
building, and there are a few things I'm not sure about. You can see a
picture of what I've come up with so far by following this link:
http://w1.894.telia.com/~u89404581/tabledesign.gif

I'll just list the things I'm not sure about:

1. Should PublicationMonth and PublicationYear be separate, or could you
combine them and use a date datatype? I'm not sure if the date datatype can
be used with only month and year. Maybe the months and years could even be
in separate tables. The month table could have a MonthID and a MonthName.
What do you think?

2.I'm not sure what to do about the imprints and publishers. A publisher can
have several imprints, but small ones may not have any imprints at all. So a
book edition can be of an imprint that belongs to a specific publisher, or
it may only have a publisher and no imprint. Any thoughts about how to best
do this would be welcome.

3. A topic can have several subtopics, and those can also have subtopics.
How would I implement this? Having tables for Topics, SubTopics, and
SubSubTopics doesn't seem very appealing. When doing searches I just want to
be able to search for a topic and not worry about if it is a subtopic or
not.

4. I'm having the same problem with series. Some books are part of a series.
And weirdly enough, some series are part of another series. So a series can
have a "subseries." I assume this would be solved the same way as the
topics.

Any other suggested you might have would of course be welcome too.

Thank you,
Jan

 

[Jan Stenqvist]

Does this look okay as it is?

Use a table like this
SubTopics
ParentTopicID - FK into Topics
SubTopicID - FK into Topics
(Both these fields in the PK, or in a "no duplicates" index)
to track the relationship between topics.

I seem to have trouble wrapping my brain around this one. Could you explain
further how this works? I need to figure out how to best implement this in a
form too.

1) Presumably you have more fields in the Authors table than shown, so
you can distinguish between different authors with the same name, the
same author with different names (e.g. James/Jan Morris) or pseudonyms,
and at least some of the different Anons. Also, what about corporate
authors and other authors whose names don't fit the "first last"
pattern? Do you need to distinguish between authors and editors? Authors
of prefaces/introductions? Names of translators?

2) Since you're distinguishing between books and editions, how will you
handle:
-the same book published under different titles in different places
-editions in different languages
-omnibuses (books containing two or more books that have previously been
published separately)
-collections and anthologies
-multi-volume editions

This database will only contain fiction and only in one language, but you do
bring up some things I hadn't considered. The primary reason I distinguish
between books and editions is just because of the cover types (hardcover,
paperback). Omnibuses and anthologies are something I have to figure out how
to handle, as well as pseudonyms, and possibly authors of
prefaces/introductions.

Any hints would be appreciated.

/Jan