Page 1 of 1

seeking advice on database design

Posted: Mon Oct 16, 2006 4:16 pm
by konstandinos
hello folk

i am building a website application that allows users to navigate through a list of motorbikes (new and used) as well as quad-bikes, and then accessories for bikers. each of these can be further defined into a make/brand (ie: suzuki, yahama, ducati), and each specific entity is associated to an image (large as well as thumbnail).

here are my thoughts on the database schema. your feedback would be appreciated.

table1: images (used to store all images, large as well as thumbnails)
attributes: image_id, image_name, image_size, image_type, image_blob

table2: brands (used to store list of makes, such as ducati and suzuki)
attributes: brand_id, brand_name, image_id (the id corresponds to an image in the image table of the brand's logo)

table3: categories (ie: new bike, used bike, quad bike, accessory)
attributes: category_id, category_name

table4: products (the heart of the database)
attributes: product_id, product_name, product_description, product_category_id (so i know which category product falls into), product_image_id (its corresponding picture), product_brand_id (its brand), product_price

ok is this a good or bad schema? and what's the best practise on handling thumbnails as well as full sized images?

please bear in mind that i have to design the content management side too for the administrator to take over the running of the site.

thanks a bunch

Posted: Sat Nov 11, 2006 8:00 pm
by califdon
I can't believe nobody has responded to you in the forum.

Your schema looks logical to me. I don't see any inconsistencies or obvious denormalizations.

My only question would be the wisdom of actually storing images as blobs. Of course it can be done, but I have heard a lot of developers say they recommend storing only file paths to the actual images, to hold down the size of the database. No doubt there are counterarguments, and I have never used images in a database application, so there may be other viewpoints. In either case, you have to handle the image file uploading, anyway.

If I understand one part of your question about images and thumbnails, I would definitely recommend creating thumbnails if you're going to display full images on a different web page than the thumbnails, because of the load time for loading and resizing a large image. I guess even if they were displayed on the same page, I wouldn't recommend resizing, because there's no sense in loading a large image if the viewer may choose not to enlarge the thumbnail.

Posted: Sat Nov 11, 2006 8:49 pm
by fractalvibes
Not a bad start. Brands and categories are essentially "domain" tables. Products are the individual items that users will be posting.

For the images table, I would also store the product_id, because people will often want muliple images of the merchandise, taken from various angles, etc.

Thinking of categories - you might need a xref table here, as a product might actually fit several categories. i.e new quadbike,used quadbike....actually I would add a new/used column to the product table and leave the category alone.

Most folks do just store the file path to the image rather than the blob, I've done both (in DB2). If you have a dedicated DB server, storing the thing in a blob along with content type can be nice, as one page can serve as a universal binary media server - whether it be an image, and mp3 file, an excel spreadsheet, etc.

Probably more efficient to create the thumbnails when you initially upload the images - you will need to add columns to the image table to indicate full-size vs thumbnail and a way to associate one to the other....

fv