HI,

I'm looking for suggestions on the best way (someone's probably already done this) to set up a database with parts for motorcycles where a user inputs make model and year and the resultant search would give them the parts which match their particular cycle.

thanks in advance for any help/suggestions

MIchael

    table makes
    make_id int autoinc primary key
    make varchar

    table models
    model_id int autoinc primaru key
    make_id int foreign key on table makes
    model varchar
    year char

    table parts
    part_id int autoinc primary key
    manufacturer varchar
    part_no varchar
    part varchar

    table bike_parts
    bp_id int autoinc primary key
    part_id int foreign key on table parts
    model_id int foreign key on table models

    Actually you would have lots of other fields in some of those tables, especially the parts table. Things like the type of part, dimensions, color, size, manufacturer, price, stock no, order lead time, stock on hand, shipping costs, etc would be essential for proper stock control.

    The table that links part to models will have mupltiple entries for any given part or model, obviously. Some parts would fit multiple makes and models and years, while some models would have several entries for the same part from different manufacturers or for different qualities/prices, etc.

      You will have 3 tables here

      table: make with make_id
      table: model with model_id and make_id
      table cars: with car_id and model_id

      When you add a car to the database, you will have two drop down menus, make and model

      you will have to select a make from a drop down menu of make, and then a new model drop down menu will show based on the make you select. that model drop down menu has model_id info. so when you add the car, the model_id will be add to the car table too.

      Of course, the other fields such as miles, and year etc.

      When search, if people just pick up the make, you pick all the cars with models_id belongs to that make. if people pick up the model, then select the cars with the selecte models_id.

      Or you can also design the car table with cars_id, make_id, and model_id, so when search by make only, you just need to select the cars from cars table with that make_id.

        genabit:

        Well, it is kind of hard to give you much help/suggestions with such a broad description of what it is you want to do.

        If yo are talking about setting up a database for say the top 5 or 10 most popular motorcycles manufactured during the last 5 years or so, and you are only going to include the major components that is one thing. However, if you want to include all parts for all motorcycles for all years, then I think that is a whole different situation.

        I am sure you are correct in your statement that someone has already done this. At least every major motorcycle manufacturer would have a database for most if not all of the parts for their particular line of motorcycles. And there are a lot of online motorcycle parts distributors that have quite extensive parts databases. But for an individual to undertake such a task would probably be impossible.

        Where are you going to get all the part numbers and the descriptions for all the parts? And that is just one of your problems. You certainly won't be able to use MySql for this if it is to include all motorcycles. You would need something like Oracle and the cost of the software is going to be substantial. Furthermore, unless you have extensive experience in database design you wouldn't be able to set something like that up.

        I don't mean to rain on your parade, but it sounds to me like a bit more of a project than the average person would attempt.

        I don't know what else to tell you except Good Luck.

          thanks, that looks closest to what I intended and yes, my parts database includes stock numbers, lead times, shipping costs, etc.

          Thanks for the suggestions.

          Roger Ramjet wrote:

          table makes
          make_id int autoinc primary key
          make varchar

          table models
          model_id int autoinc primaru key
          make_id int foreign key on table makes
          model varchar
          year char

          table parts
          part_id int autoinc primary key
          manufacturer varchar
          part_no varchar
          part varchar

          table bike_parts
          bp_id int autoinc primary key
          part_id int foreign key on table parts
          model_id int foreign key on table models

          Actually you would have lots of other fields in some of those tables, especially the parts table. Things like the type of part, dimensions, color, size, manufacturer, price, stock no, order lead time, stock on hand, shipping costs, etc would be essential for proper stock control.

          The table that links part to models will have mupltiple entries for any given part or model, obviously. Some parts would fit multiple makes and models and years, while some models would have several entries for the same part from different manufacturers or for different qualities/prices, etc.

            Write a Reply...