SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    book store db design- thinking out loud

    Here are a few ideas for a books table in a db that will service and online bookstore. I'm a newbie with database design so I'd like to think out loud here and get whatever comments from the seasoned pros here at sitepoint forums.

    The question I'm trying to deal with has to do with the cataloging of hard cover and soft cover books. Lots of data can be shared between a soft cover book and it's hard cover brother. But some can't.
    So there seem to be three ways to do this.

    option 1)


    Code:
    book_id			autonumber
    pub_book_id		text
    isbn			text
    price			currency
    title			text
    weight			number	
    dimensions		text
    subtitle		text
    short_description	memo
    long_description	memo
    publisher_id		number
    soft_cover		yes/no

    hard cover and soft cover books of same title get seperate listings. This way you get about double the number of listings in the database. And huge fields, like long_description are repeated. Updates are also double work. For sure this isn't the right way to go.

    ---------

    option 2)

    Code:
    book_id			autonumber
    pub_book_id_hc		text
    pub_book_id_sc		text
    isbn_hc			text
    isbn_sc			text
    price_hc		currency	
    price_sc		currency
    weight_hc		number
    weight_sc		number
    dimensions_hc		text
    dimensions_hc		text
    title			text
    subtitle		memo
    short_description	memo
    long_description	memo
    publisher_id		number


    each listing has a hard cover and soft cover field for the data that cannot be shared. Big fields are shared so the db is smaller and easier to update. But there are more fields in each table and some of them are not used.




    option 3)

    Code:
    book_id			autonumber
    pub1_book_id		text
    isbn			text
    price			currency
    title			text
    weight			number	
    dimensions		text
    subtitle		text
    short_description	memo
    long_description	memo
    publisher_id		number
    hard_cover_details_id	number	1 reserved if there is hard only
    				2 reserved if there is soft only
    				fk for hard cover details 
    					table if there are both.




    seperate table for details that cannot be shared. Like option number 2 except the non sharable data is stored in a separate table. Here we have more efficient use of storage space because some books come in only one cover type.

    I figure option number three is is the most efficient in terms of keeping the database file as small as possible. But creating another table will make the queries and the asp scripts more complex, which is definitely a consideration for me, since I have enough complications in my life as it is.

    So I guess I'm wavering between options 2 and 3. Is 2 really wrong because it wastes a field where it's not always needed? Are there other considerations that I'm not aware of? Is there an alternative that I haven't thought of yet?

    I only included option number 1 because I'm thinking out loud here.


    ------

  2. #2
    Net Senior Citizen tommatthews's Avatar
    Join Date
    Apr 2001
    Location
    Sydney Australia
    Posts
    869
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Don't forget the C format paperback which is the size of a hardback but softback and would be classified differently then a A format mass market pb.


    affordable website design

    :: sydney australia ::

  3. #3
    SitePoint Enthusiast xev's Avatar
    Join Date
    Jan 2002
    Location
    USA
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like option 3 the best. But here is how I would do it:

    Code:
    book_id            autonumber
    title              text
    subtitle           memo
    short_description  memo
    long_description   memo
    publisher_id       number
    
    details_id         autonumber
    book_id            fk to book_id in other table
    cover_type         soft/hard
    pub_book_id        text
    isbn               text
    price              currency	
    weight             number
    dimensions         text
    Some things to consider first: Do both hard and soft cover books ALWAYS have the same title? Do both type of books ALWAYS have the exact same description? (I know for a fact that things sometimes change between the release of a soft cover and hard cover.) Do they ALWAYS have the same publisher? If not, you may want to simply use option 1.
    Last edited by xev; Mar 24, 2002 at 21:48.

  4. #4
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks xev, would that be pronounced zev?

    I took your approach and am doing it like this:
    Code:
    books
    
    book_id (pk)
    title
    subtitle
    short_description
    long_description
    image_file					
    image_caption
    publisher_id (fk)
    discount_id (fk)
    featured
    series_id (fk)
    
    
    issue_details
    
    issue_details_id (pk)
    book_id (fk)
    pub_item_number
    isbn
    price
    cover_type	hc,sc,lc(l for leather)
    dimensions
    pages
    weight
    outof_stock
    date_published

    As far as your questions go,
    If the same book was published by two publishers I would list it as a different book and might or might not use the same description.
    And I would always use the same description for hard and soft cover books. I can always add some kind of description to books of specific cover types as a caption under the picture from the asp code, without putting it into the database.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •