book store db design

[avikohl]

Here is a specific question about how I should represent a real life situation with database design.
The book list I'm working with has many examples of book titles shared by many books where there are many different parameters which may differentiate between them and some of those parameters are used very rarely.

For example. A particular book may be the third volume of a set. In this case "title" would be the same for all volumes and the volume number would differentiate it from the other volumes. It would be nice to have a numerical field so that ordering the volumes numerically will be simple.

Another title may come in different colors, although this is rarely the case. If I have a size field in the issueDetails table it will contian a value for a tiny percentage of the listings.

Size variation is a bit more common, but many titles have their own distinct names for the size values. One title may have large, medium and pocket size. Another would have full size and student edition. Is it worthwile creating a size and color fields when they will be used with a very small percentage of listings?

Alernatively, I could create a detailHighlights field for text values that differentiate the issues sharing title and subtitle in whatever way is appropriate for in each case. "student-size / green" or "pocket-size / three volume slip-case" This way the values differentiating each item would be contained in a single field that would have a value in most cases.

What do you suggest? Are there issues I'm missing here?


books
------
bookID
title
subtitle
shortDescription
longDescription
imageFile
publisherID



issueDetails
-------------
issueDetailsID
isbn
binding

==========
(detailHighlights)
or
(color)
(size)
(numberOfVolumes)
=============

weight
dimensions
NumberOfPages
volumeNumber
ShippingWeightCodeID
outOfStock
price

 

[2ffat]

Don't you need the ISBN number. This is the bar code on every book (except older ones.)
James P. Cottingham

When a man sits with a pretty girl for an hour, it seems like a minute. But let him sit on a hot stove for a minute and it's longer than any hour. That's relativity.
[tab][tab]Albert Einstein explaining his Theory of Relativity to a group of journalists.

 

[skiflyer]

Yeah, I notice you do have the ISBN, all the information you're talking about is in some fashion or another, retrievable from there if I'm not mistaken. Now if you're expecting your database to do some kind of intelligible sorting like all books of the same size, I don't think you can parse the ISBN usefully like that. But then, you'd be making the size category used on every book, not just rarely.

Additionally, the ISBN only goes back about 30 years or a few more with any kind of consistency.

Anyway, my opinion is, from what you seem to state as your goals, Comment field that lets someone type that stuff in if they want to, and not if they don't, that way you're covered if later someone has an edition that is only different because the publisher used orange paper and you didn't think of that when you made your DB.

-Rob

 

[avikohl]

Thanks for your comments. Your last paragraph, Rob, confirms my second option, which I am now leaning towards.

As far a ISBN is concerned, I don't think its going to function as a key for every single item that I will have in the data base. Its just one piece of data that I will record for most of the items. I assume that it might be a useful piece of information at some stage although at this stage I don't know How I will use it.

James, nice quote from Albert Einstein. A variation of that was in the movie "Deep Blue Sea"; When you have your hand on the handle of a hot frying pan a second seems like an hour. When its on a hot woman, an hour seems like a second.

Here's another nice popularization of the great Albert.

http://artists.iuma.com/site-bin/mp...m/audio/County_Farm_-_Einstein_the_Genius.mp3