SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)

    Use the adjacency model or something else for a parts list

    I have a small problem dealing with options in a parts picking list:

    Currently I have:

    1/ Table "parts" with the part - id, name, kit and price
    2/ Table "car1", "car2" etc. for each model with the parts used in the model - parts.id

    This allows for any of the parts to be used in any of the models and I use a join

    SELECT id, name, price, kit FROM parts
    JOIN
    car1
    ON
    car1.part=price.id

    I can then display each part with price etc.
    The parts are also broken down into "kits" when displayed which I mark in the parts table with W for wheels D for drive etc.

    The problem is the user can buy for example standard tyres, cross ply tyres or knobbly tyres.
    This can be set using radio buttons but what is the best way to define the options for the radio buttons in the table?

    I have tried setting the option numbers in the parts table but that did not work very well.

    I was thinking of having another table for each of the options but that means a lot more tables. I then read about the adjacency model on another post here and I wondered if it is the way to go?

    INSERT INTO `options` (`id`, `name`, `parentid`) VALUES
    (1, 'tyres', NULL),
    (2, 'front axel', NULL),
    (3, 'Control', NULL),
    (11, 'Standard', 1),
    (12, 'Cross ply', 1),
    (21, 'Fixed', 2),
    (22, 'Floating', 2),
    (31, 'Step control', 3),
    (32, 'Speed control', 3),
    (33, 'Extension lead', 32);


    This makes sense as some of the options have another option attached e.g. extension lead can only go with speed control.

    I have tried this out and it works on its own except that I need to sort out how to output the data how I want it to look. But how do I combine the options data with the other two tables as there is nothing to join?

    Should I build the original parts table using the adjacency model or perhaps do a separate query where the options are to be displayed?

    Any insight would be helpful.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Rubble View Post
    1/ Table "parts" with the part - id, name, kit and price
    2/ Table "car1", "car2" etc. for each model with the parts used in the model - parts.id
    you're in trouble already -- there should really only be one car table


    Quote Originally Posted by Rubble View Post
    This can be set using radio buttons but what is the best way to define the options for the radio buttons in the table?
    the same way you would define the options for radio buttons in a country table -- i.e. you don't


    Quote Originally Posted by Rubble View Post
    I then read about the adjacency model on another post here and I wondered if it is the way to go?
    only if one part is a sub-part of another part, e.g. if you had a windshield wiper kit and you also sold the blade, the arm, and the attaching gasket separately


    Quote Originally Posted by Rubble View Post
    I need to sort out how to output the data how I want it to look.
    that's a good idea, work on that and then get back to us

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    you're in trouble already -- there should really only be one car table
    Are you suggesting I should have a table with the models e.g 1-8 and a table with the parts 1-300 and a table between linking the parts to the models?

    ------part_id-----|------model_id------|
    --------1----------|-----------1------------|
    --------1----------|-----------3------------|
    --------2----------|-----------1------------|
    --------3----------|-----------1------------|
    --------3----------|-----------2------------|
    --------3----------|-----------3------------|

    the same way you would define the options for radio buttons in a country table -- i.e. you don't
    I did not word that very well what I ment was how to get the data from the table that links the options together so that I can make radio buttons from the data.

    only if one part is a sub-part of another part, e.g. if you had a windshield wiper kit and you also sold the blade, the arm, and the attaching gasket separately
    The user can buy anything from one part to the whole kit. Some items like the tyres they buy one or the other, with the controller they can buy the basic step controler OR the speed controller. If they buy the speed controller they can then buy the extension lead. The extension lead will not work with the step controller so it should not be displayed.

    that's a good idea, work on that and then get back to us
    I know how the data is going to be displayed but the fine detail depends on what data I get from the table.

    options1.jpg]

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Rubble View Post
    ... and a table between linking the parts to the models?
    that works very well, a classic many-to-many relationship


    Quote Originally Posted by Rubble View Post
    I did not word that very well what I ment was how to get the data from the table that links the options together so that I can make radio buttons from the data.
    with a query

    that might sound sarcastic but it isn't

    i'm not sure i understand what you're not seeing here


    Quote Originally Posted by Rubble View Post
    I know how the data is going to be displayed but the fine detail depends on what data I get from the table.
    in that attachment, you have two radio buttons and a checkbox

    please allow me to say as politely as i can that this user interface is confusing at best

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    that works very well, a classic many-to-many relationship
    How will the options get included into that? Make it a adjacency model table?

    The interface is a problem - the site owner wanted a page for every part and option which would have been 60+ pages for every model!
    It would work in one way - when the user arrived at the control page he would pick step controller and then wheels. If they picked speed controller they would go to hand held box, extension lead and then wheels.
    This would give a nice interface but personally I would give up after a couple of pages. I wonder if this could be done in JavaScript but I know very little about it and if the user goes to the confirmation page and decides to make a change and goes back how would that work?

    The radio/check box is so the user has an option with the radio button of two or three items but he will only want one of the options. The checkbox is not an option and the user can pick it or leave it; I can not have all check boxes as the users will end up picking both items; belive me this will happen.

    Pullo has helped me with some javascript that will hide or show the sub items in this thread and there is a link to a demo near the middle of the thread.

  6. #6
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,435
    Mentioned
    82 Post(s)
    Tagged
    3 Thread(s)
    I should say if anyone has a better interface idea I am open to suggestions.

    Just need to bear in mind that I tried increasing the size of the forum on the screen a couple of years ago and I had a load of complaints from users who were still using 800x600 displays.


Bookmarks

Posting Permissions

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