seeking advice on database design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
konstandinos
Forum Commoner
Posts: 68
Joined: Wed Oct 04, 2006 4:20 am

seeking advice on database design

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
Post Reply