Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Question Unanswered: Help with SQL statement & database structure

    Hello,
    I've been struggling with how to write a sql statement. In my webpage, I have a treeview control. Each root level node is a category (eg. fruits, vegetables) and each subnode is an item. For instance, under fruits, we'd have strawberries, kiwi, grapes, etc. and under vegetables, we'd have broccoli, cauliflower, carrot, etc.)

    I know the proper way of creating the tables would be to create a fruits table and a vegetable table, that way when I add more items, I simply add them to the proper table.

    My problem is this: I want the users to be able to click on a checkbox next to the treeview item and have the webpage update (through ajax) another section of the page that displays pictures of the food items and some other info. Multiple checkboxes can be checked at once, and I need the update panel to show the filtered output.

    For instance, let's say the user clicks on strawberry and carrot. The update panel should now only show a picture of a strawberry & a picture of a carrot (& some other info).

    I figured out that if I had everything in one table (horrible design), I could use the union statement and get what I'm after. For instance,
    select * from fooditems where fruit='strawberry'
    union
    select * from fooditems where vegetable='carrot'.

    There's got to be a better way.


    My database structure so far (simplified for brevity):
    fooditems table
    fooditemID, fooditem, etc1, etc2,...

    vegetables table
    vegID, fooditemID, vegName

    fruits table
    fruitsID, fooditemID, fruitName

    Now to complicate matters, I want to be able to filter items by the color as well. A lot of the food items will have multiple colors (eg. pumpkins can be white, green, orange, etc.). I'm not sure if this matters for database design & sql query, but I only want one picture of a particular food item to show at once, but under the picture, have the available colors show in little boxes and then when users click on the box, the fooditem will change to the appropriately colored picture.

    So, I added another table:
    colors table
    colorsID, fooditemID, color

    As an FYI, I'm using SQL Server 2005 & my dev language is C#.

    I graciously ask for some help in setting up the database structure and sql query.

    Thank you.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7
    been there done that

    break the database down to parent nodes then child nodes

    1st query parent node then use that to query (where) the child nodes

    use the eof (End Of File)

    I use Obout.com Tree view they Have some good ideas to help you

    eg

    query the parent node until EOF
    each pass passing the parent node to the child node until EOF
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bubbagump370 View Post
    I know the proper way of creating the tables would be to create a fruits table and a vegetable table
    No - it's not.

    Quote Originally Posted by bubbagump370 View Post
    I figured out that if I had everything in one table (horrible design), I could use the union statement and get what I'm after.
    Not horrible, correct. And you could use a union, but you wouldn't.

    Colour and item type (i.e. vegetable or fruit) are attributes of the item.

    Item {ItemName, ItemType, Colour}
    ItemType is either vegetable or fruit. You can have a colour lookup table, and an ItemType lookup table and relate these to this table if you like. You might want to record all the colours of an item, in which case you will need an item colour table.

    The only reason you would need a fruit table and a vegetable table is if there are attributes you need to record specific to vegetables and specific to fruit. If that is the case then this needs refining.

    Get your design correct, then the SQL logically follows from that. Don't concern yourself with the front end display initially.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2010
    Posts
    3
    Thank you for the great tips, I will use your advice.

    Ok, I understand why its not horrible to include ItemType in my FoodItems table...because there are only two available options for it (veg or fruit). I was trying to prevent duplication of data.

    So if I understand this correctly, given that there can be many different colors for a given food item, those colors should be listed in a separate table, with a link back to the FoodItems table. This prevents a whole bunch of data duplication in the FoodItems table.

    Is it safe to say that if there are multiple options for a food item (the food item could simultaneously be many things) that those options should go in a separate table? i.e. an apple could be green & red, so there should be a colors table with a FoodItemID linking it back to the FoodItems table? This would prevent an exact duplicate of the FoodItem table row with only the color changed.

    To better my understanding, let's say that I also want to ship the food items out in a certain type of packaging. Some food items might only have one type of packaging, but others could have more. If I'm understanding everything correctly, I'd put the packaging type in a separate table, and link it back to the FoodItems table with the FoodItemID. Is this the proper way?

    Finally, if I design a database like this:
    FoodItem {FoodItemID, ItemName, ItemType}
    Colors {ColorsID, FoodItemID, Color}
    Packaging {PackagingID, FoodItemID, BoxSize}
    How would I write the query so that when a user clicks on the treeview control checkboxes, the proper selection occurs? Is this possible with one sql statement? or would I have to do some sort of looping through the dataset?

    I did some testing & this seems to work, but I would like to verify that this is the best way of doing it:
    SELECT FoodItemID, ItemName, ItemType
    FROM FoodItem
    WHERE FoodItemID IN (SELECT FoodItemID FROM colors WHERE color = 'red') AND FoodItemID IN (SELECT FoodItemID FROM Packaging WHERE BoxSize = 'small')
    Then, in my programming code, for each record, I could do another query, pulling all of the available colors for a given food item.

    Thank you

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good - I think you are getting the idea. However, as said it is imperative you finalise your design before writing SQL. The design is still not correct so your SQL is, sadly, a waste of your time.

    Let me try to formalise your thoughts.

    Duplication of data is not to be feared, at least not in the way that you are thinking about it. Having the same value occur many times in a column is not duplication of data. In fact, you expect there to be repetition in all columns that are not single column candidate keys.

    With regard to your problem, you have many items. You have many colours. Any single item will be one or many colours. Any colour be found on none, one or many items. This means there is a many-to-many relationship between colours and items. The same is true of packaging.

    Have a read of this. It is the best crash course I know on database design & normalisation. Try to apply each part as you read it to your problem
    The Relational Data Model, Normalisation and effective Database Design
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2010
    Posts
    3
    Thank you, your responses definitely have helped.

Tags for this Thread

Posting Permissions

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