Hi,

Without confusing anyone, I will just tell you my problem and I will see if you think you know the proper route to take. I have a website, www.somethingspecialgiftbaskets.com

it is currently static, and I want to make it database driven.

Well, I am giong to be using Coldfusion and ms access 2002.

if you go to www.somethingspecialgiftbaskets.com you will see the site I am doing.

I have my menu with basket categories.

I have in each of those categories baskets relating to those categories.

Each basket contains its own individual page containing an image, a title, a summary, the baskets contents, a price and thats it.

I plan on using forms to be able to update things like description, price, titles, etc.. on the web.

Now because I am not dealing with anything like customer names, credit card numbers, login, passwords, etc.. I don't know how deep I need to take the table structure on this one. I have had some good recommendations, but I think I am getting more confused and I think I need a fresh start at the design.

Here is what I am looking at trying to accomplish.

Someone comes to the main page, they click on a category, like (any occasion). The page that opens up lists ALL baskets located in that category. They click on a basket name. The page that opens up contains everything I mentioned above: title, price, summary, contents, etc..

I also have a featured baskets section on the front page, but I felt that you could just pull information from a table already containing information about the baskets and just show only the picture and title.

To be as least confusing as possible, here is what I have started with so far.

Database: something

Table: baskets

Fields:

basketID
basketname
basketsummary
basketprice
smallimagepath
largeimagepath
categoryID

and that is where I stop, because I get more lost if I delve any further.

Thanks for any help you can provide.

Bryan

I am going to assume you want to make the baskets up from smaller items that you may or may not want to sell separately. Like if you have a basket called "Republican Party Pack" that contains Balloons, Hats, Condoms and Lubricant, then you might want to sell these items separately as well as in the basket.

So I would do this:

ITEMS
item_id
item_description
small_image_path
large_image_path
price

BASKETS
basket_id
basket_name
basket_summary
basket_price
category_id

LINKS
link_id
item_id
basket_id

CATEGORIES
category_id
category_name
category_description

This way you can add and remove items from each basket, update an item that is in several baskets without having to alter the description of each basket individually, allow customers to view info on each item separately and update all basket, item and category information from simple forms.

Hope this helps.

Thanks for the reply naz,

We don't plan on selling the items individually, but its a good thought.

Even if you dont plan on selling the items separately this is still a good idea as if an included item changes you dont have to change the description for all the baskets as it will change automatically for all baskets that include it.

You know, you are pretty much right.

Currently, I have a many to many relationship. This is what I have

table: baskets

basketID
blah
blah

table: basket_content

basketID
itemID

table: content_list

itemID
itemname

Obvioulsy the ID's are all linked together and the basketID and itemID in basket_content are primary keys.

I was reading in Ben Fortas Coldfusion WACK that generally if a many to many relationship is used, it is a bad database design. Typically you can build a good database using multiple 1 to many relationships.

Your thoughts.

I think I basically need to break this thing down so that a 2 year old can understand it.

Anyone who says a many to many relationship is a sign of bad design is just plain wrong. There are many instances where many to many relationships are natural.

I think in this case if you used a many to many for baskets and items it would be the best way, as each basket could have several items, and each item could be in several basket. If an item changed then you could jsut change the item description rather than changing the description for every basket that contained it.

The schema I suggested in my first post works for this perfectly, the one you posted is the same thing, just a many to many.

Breaking this into multiple one to many relationships will increase your complexity as well as reduce the db performance. It would also be a dumb idea given that a perfectly natural and elegant solution has already been arrived at.

Hope I've been helpful.

Yes you have. Thank you

Let me go ahead and post what I currently have as my database structure.


Table: baskets

basketID (primarykey)
basketname
catimagepath
smallimagepath
largeimagepath
basketprice
categoryID
basketsummary
catdescription

Table: basket_content

basketID
itemID

BOTH ARE PRIMARY KEYS

Table:categories

categoryID (primary key)
categoryname

Table:content_list

itemID (primary key)
itemname


When you used this:

LINKS
link_id
item_id
basket_id

I am assuming that you just used link_id as the primary for the table unlike in mine where the primary key is both of the fields. Is your way better? If so, why.

Do you see anything in this structure that you feel could be altered, added, taken out, etc..?

Thanks
Bryan

using the linkID is called a surrogate key. it is a bad habit i picked up along the way. your way is better, if you have a natural key such as the basketID and itemID combination it is better to use that.

sorry for confusing you.

oh yea, and the shcema looks fine as is now