Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Database design problem, need some help

    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

  2. #2
    Join Date
    Jul 2003
    Posts
    18
    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.

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    Thanks for the reply naz,

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

  4. #4
    Join Date
    Jul 2003
    Posts
    18
    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.

  5. #5
    Join Date
    Sep 2003
    Posts
    6
    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.

  6. #6
    Join Date
    Jul 2003
    Posts
    18
    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.

  7. #7
    Join Date
    Sep 2003
    Posts
    6
    Yes you have. Thank you

  8. #8
    Join Date
    Sep 2003
    Posts
    6
    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

  9. #9
    Join Date
    Jul 2003
    Posts
    18
    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

Posting Permissions

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