how best to create tables

[tyutghf]

I am trying to come up with a database plan for a product database.

We need to store

product info such as name, price, description, product category (each can be in multiple categories), manufacturer
a list of available categories
product images
manufacturer info

How is best to structure this in a database so that it will be efficient (and easier to code haha) to input, store and link this info? Here's what I am thinking...

Product (basic product info)

id
title
description
price
manufacturer (link to manufacturer table > id)


Category list (list of available categories)

id
category name

Product categories (each product can have multiple categories so this is to link the two)

id
productid (link to product table > id)
categoryid (link to product table > id)

Product images (each product can have lots of images)

id
image name
image file
productid (link to product table > id)

Manufacturer table

id
name
description
image
location

is that about right or would you folks do it better?

I will be creating a php page with text input, textareas for the info areas, dropdown for the manufacturer and checkboxes for the categories to add all of this data.

It will be displayed on an internal webpage where users can search through categories, manufacturers, price etc.

Thanks

 

[r937]

you are clearly on the right track

however, i would eliminate the "id" column in the product categories table, and assign the pair of FKs as the PK

i would also eliminate the "id" column in the product images table, and assign the PK as productid plus image name

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[tyutghf]

Thanks, I suppose I didn'tneed the ID's on those tables.