Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Product database design questions re. asp (2002)

    Hi,
    I am currently designing a product database which is to be the data for an asp database. I just want to ask a few questions to make sure im doing it the smartest way possible.

    The product structure on the site will be something like this:
    (It will be one of those expanding css type menus so it will all be loded on the first page)

    Cameras
    --- Digital Cameras
    --- Flash Memory
    Computer Systems
    Computer Hardware
    --- Accessories
    --- Add on Cards
    --- Drawing Tablets, Digitisers
    --- Gaming Accessories
    ------ Controllers
    ------ Display Adapters*
    --- Home Entertainment
    --- Keyboards & Mice
    --- Laptop Accessories
    ------ HDD
    ------ RAM
    --- Memory
    --- Modems
    ------ Routers*
    ------ ADSL
    ------ Internal
    ------ External
    --- Monitor
    ------ LCD
    ------ CRT
    --- Networking
    ------ Wireless
    ------ Hubs / Switches
    ------ Cables
    ------ Routers
    --- Printers
    --- Projectors
    --- Scanners
    --- Soundcards
    --- Speakers & Headsets
    --- Storage
    ------ Hard Disks
    ------ Optical Drives
    ------ Removable Media
    ------ Floppy Disk Drives
    ------ Flash Memory
    --- Backup
    --- System Components
    ------ Cases, PSU & Accessories
    ------ Cooling
    ------ CPUS
    ------ SCSI / RAID adapters
    ------ Motherboards
    --- TV Tuner/Video Capture
    Handheld Computers
    --- Ipaq
    --- Palm
    --- Sony
    ---Sharp
    --- Etc
    Hosting
    Software

    So the most it ever goes is 3 brances deep. I wanted to make this tree generated competely automatically.
    All the products will be in one table. So i figure the best way to make this tree self generating, would be to make 3 tables for the different levels.
    Call the first table LEVEL1 and make a list of the main categories (Cameras, Computer Systems, Computer Hardware, Handheld, Hosting, Software)

    Then make LEVEL2 with two fields, one being the 2nd level menu e.g. Gaming Accessories. The second field being a dropdown list of LEVEL1 fields (so "Computer Hardware")

    The make a third table LEVEL3 with two fields, the first being "controllers" the second being a dropdown of LEVEL2 (i.e. Gaming Accessories)

    That way i could do sql to write out the menu. Is this the best way?
    And heres the big question: Can anyone think of a way to make it work with unlimited branches?!?!

    Adrian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Product database design questions re. asp (2002)

    If you want to make it unlimited, you should use one table, with a structure as follows:

    <table border=1><td align=center>ID</td><td align = center>ProductName</td><td align = center>ParentID</td><tr><td align=right>1</td><td>Computer Hardware</td><td align=right>

  3. #3
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Product database design questions re. asp (2002)

    Brilliant!

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Product database design questions re. asp (2002)

    In case you need it, here is some code. It uses DAO, but it can be adapted to use ADO instead. Let's say that your table is named tblProducts. And it is VBA, but it should work in VBScript in an ASP page too.

    Sub GetTopLevel()
    Dim rs As Object
    Set rs =CurrentDb.OpenRecordset _
    ("SELECT * FROM tblProducts WHERE ParentID Is Null", _
    dbOpenForwardOnly)
    Do While Not rs.EOF
    ' Do something with the record here
    Debug.Print rs("ProductName")
    ' Get children (recursive call)
    GetChildren rs("ID"), 1
    rs.MoveNext
    Loop
    Set rs = Nothing
    End Sub

    Sub GetChildren(PID, Level)
    Dim rs As Object
    Set rs =CurrentDb.OpenRecordset _
    ("SELECT * FROM tblProducts WHERE ParentID=" & PID, _
    dbOpenForwardOnly)
    Do While Not rs.EOF
    ' Do something with the record here
    Debug.Print "Level " & Level & ": " & rs("ProductName")
    ' Get children (recursive call)
    GetChildren rs("ID"), Level + 1
    rs.MoveNext
    Loop
    Set rs = Nothing
    End Sub

  5. #5
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Product database design questions re. asp (2002)

    A thought...
    The products arent going to be listed on this tree.
    Since these categorys are now in with the product table what is the best way to distinguish the products and make the tree not list them?
    Thanks
    Adrian

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Product database design questions re. asp (2002)

    You can add a Yes/No field IsProduct to the table; set this to Yes for the records representing products. In both procedures, add a condition IsProduct = False to the SQL string:

    "SELECT * FROM tblProducts WHERE ParentID Is Null And IsProduct = False"
    and
    "SELECT * FROM tblProducts WHERE ParentID=" & PID & " And IsProduct = False"

  7. #7
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Product database design questions re. asp (2002)

    Do I need to open a connection to the record set or something?
    I get an invailid argument message on these lines.

    Set rs = CurrentDb.OpenRecordset _
    ("SELECT * FROM tblProducts WHERE ParentID Is Null", _
    dbOpenForwardOnly)

    This code lookes really different is that because its in DAO? Do i need to do anything special to make DAO code work?

    Adrian

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Product database design questions re. asp (2002)

    Are you doing this in an ASP page now, or are you practicing in Access?

  9. #9
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Product database design questions re. asp (2002)

    Ok ive just realised that I definately need to open the connection, this is how i did it:

    Dim CurrentDb As ADODB.Connection
    Dim OpenRecordset As New ADODB.Recordset

    Set CurrentDb = CurrentProject.Connection
    OpenRecordset.Open "tblProducts", CurrentDb, adOpenForwardOnly

    Dim rs As Object
    Set rs = CurrentDb.OpenRecordset _
    ("SELECT * FROM tblProducts WHERE ParentID Is Null", _
    dbOpenForwardOnly)

    Now i am getting a "wrong type, out of range or in conflict" error for the same bit of code.

    I am trying to get it to work in Access first.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Product database design questions re. asp (2002)

    You're mixing ADO and DAO code now; moreover CurrentDb and OpenRecordset are defined in Access VBA, so you shouldn't use them as names for variables. Try this:

    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set cn = CurrentProject.Connection
    rs.Open "SELECT * FROM tblProducts WHERE ParentID Is Null", cn, adOpenForwardOnly

  11. #11
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Product database design questions re. asp (2002)

    Gotcha


    Sub GetChildren(PID, Level)
    rs.Open "SELECT * FROM tblProducts WHERE ParentID=" & PID, cn, adOpenForwardOnly

    What does the PID mean?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Product database design questions re. asp (2002)

    We pass the ID of the current record to the GetChildren procedure as the PID argument; this is used to open a record set of all records whose ParentID is equal to the value of PID, i.e. of the ID in the current record.

    Example: if the current record has ID=4 ("Cases" in my example higher on in this thread), GetChildren is called with PID = 4. It opens a recordset of all records whose ParentID=4, i.e. "PSU" etc.

  13. #13
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Product database design questions re. asp (2002)

    I get "Datatype missmatch in criterior expression" for the line in the previous post.
    Could that be because I have ParentID as being 'text'.
    I assume it is. I have changed it to 'number' and it doesnt come up with errors.

    My question is [img]/forums/images/smilies/smile.gif[/img] how can i see the debug lines it's supposed to print
    Thanks
    Adrian

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Product database design questions re. asp (2002)

    I had assumed that your ID field was numeric.

    If an error occurs, you should see get an error message with button End, Debug and Help. If you click Debug, you're taken to the Visual Basic Editor. The code is paused with the offending line highlighted. You can try to repair the code and press F5 to continue (or F8 to execute step by step), or you can click the Stop button.

  15. #15
    Star Lounger
    Join Date
    Oct 2002
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Product database design questions re. asp (2002)

    I meant how can i see the Debug.Print output?

Page 1 of 2 12 LastLast

Posting Permissions

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