Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    4

    How to model optional elements?

    Hi guys

    Just a quick question that I hope someone can help me with.

    So, I am trying to design a database which mimics the market in EVE-ONLINE, a space combat/trading game.

    The market is split into categories.
    The items are in seperate sub-categories.

    This would in itself be relatively easy to do, but the problem comes when some items are in a Category - Sub Category, while others are in a Category - Sub Category - Sub Category.

    Basically some items are organised into a 2 tier category, some a 3 tier, and there is noway of knowing if any item will be added in the future that make it a 4 tier category.

    So my question is, how do I model this accurately.

    Hope that makes sense.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    what you probably need is some form of self referencing column (ie aq column which refers to another column in the same table
    eg..
    tblCategory
    CatID autonumber PK
    CatDesc varchar
    ParentCatID Integer FK to CatID in same table

    1 | Food | Null
    2 | Clothing | Null
    3 | Deserts | 1
    4 | Appetisers | 1
    5 | Savoury | 3

    the downside is that it may take slightly longer to iterate down the tree
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    iterating down the tree is an interesting problem, discussed in some detail in this article: Categories and Subcategories

    in this particular case, your items would then reference the category or subcategory or subsubcategory the same way -- with a foreign key to CatID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Infinate teirs of categories...

    Err: How about a table like this:
    Heirachy(ParentID, ChildID)
    Note that the key is combined.

    I'm not the best at modelling, but this is the solution I can think of!
    EDIT: Doesn't need to be a new table, you could jsut store the parent (or child) ID in the same table - but this way ensures that you don't get two items with the same parent and child

    p
    / \
    c c
    \ /
    gc
    George
    Home | Blog

  5. #5
    Join Date
    Apr 2007
    Posts
    4
    Hi guys.

    Thanks for the prompt reply and links, I hoped you would say this as it was the conclusion I had come to.

    I,m still unsure if a database query can sort it for me or if I would have to do this with a PHP script, but now I know my thinking was correct I can test it.

    Thanks again.

  6. #6
    Join Date
    Apr 2007
    Posts
    4
    Hi again.

    Slightly of the database topic, but still relevant to my problem, does anybody have an elegant method of producing a form that can handle the selection and input?

    Thanks

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    the selection and input of what
    a form for what development environment (VC++, VB, VStudio, Access, Delphi.....)

    one of the design methodologies Ive seen work quite well is either a collapsable tree control or say 2 list boxes with a clickable text above
    eg

    Food -> Deserts -> Hot Deserts -> Pies & Pastries
    in fact its very similar to the clickable text you see on this web page#
    "dBforums > General > Database Concepts & Design > How to model optional elements? > Reply to Thread"

    List Box 1: contains all the sub categories of the current parent
    List Box 2: contains all sub categories for the selected item in ListBox1
    the text contains the full navigation to get to the current master category

    in the above example it would contains all Pies & Pastries

    I think the smarter style is going to be a collapsable tree control. its more intuitive for the user, easier to set up, once set up works fien (theres no interaction required between controls.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Apr 2007
    Posts
    4
    Hi

    Basically the form will be in HTML, I did think about dropdown selection boxes, where the one to the right would update depending on what was selected in the first, however this would involve javascript and there are two problems with this, I dont know it and not everyone has it enabled, but would be the more elegant solution.

    I think I am going to go with a simple process of adding a drop down selection box each time the page is updated until there is something input into a form field, which would indicate that the desired category level has been reached and a new category should be created.

    Unless anyone has a better idea.

    Thanks

Posting Permissions

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