It does sound to me like something that would need new code, be it custom or future core as you say. But I think it’s perfectly doable in principle.
Caveat: I’m a relative newbie to Magento.
I think “configurable product” is probably the wrong name for it. What you have is a search problem based on matching one of several alternative product attributes. The product itself is simple.
At the moment Magento allows products to have attributes. But as far as I know (based on my newbie level of Magento familarity), each attribute can have only one value.
Ideally you want an attribute which is itself a table of bike, year and model. (In practice you wouldn’t have a separate table for each product, you’d have all of the products’ data in one big table, labelled/distinguished by SKU. But conceptually, it’s a table per product.)
In terms of the database structure, one way to do it would be like this:
Create a new table called something like relevance_of_product_to_bikes.
In that, you would have fields
| product type | sku | bike | year | model |
and the populated table might look something like this:
| brake pad | BP1000 | Honda | CBR600 | 2003 |
| brake pad | BP1000 | Honda | CBR600 | 2004 |
| brake pad | BP1000 | Honda | CBR600 | 2005 |
| brake pad | BP2000 | Kawasaki | abc | 2005 |
| brake pad | BP2000 | Kawasaki | xyz | 2006 |
| headlight bulb | HB1000 | Kawasaki | abc |
and so on.
(see how the first three lines there are all the bikes for BP1000 - that’s what I meant by it being conceptually one table per product - those three lines of the big table are like a mini-table just for BP1000.)
When you come to make e.g. the “brake pads” page, you’ve got a choice of how to populate your droppy-downers.
You could do them based on three queries, each one for the unique values of one field,
WHERE “product type” = “brake pad”
So e.g. if that tiny table was all you had, the droppy downers for brake pads would be
| Honda |
| Kawasaki |
| CBR600 |
| abc |
| xyz |
| 2003 |
| 2004 |
| 2005 |
Or, more elegant if you’re going that way, make each query build on the next, so that e.g. after they’ve selected Kawasaki, they don’t get offered CBR600, since that’s a Honda model.
so it would be like:
query all brake pads > offer Honda and Kawasaki
customer chooses Kawasaki > query all Kawasaki brake pads > offer abc and xyz
customer chooses xyz > query all Kawasaki xyz brake pads > offer years
(OK in this example table there was only one year but you see what I mean)
customer chooses year > query to identify product
(you’d also need a link saying something like “My bike is not here!” for the case where you don’t have the product for that person’s bike, which would take them to a page where they could contact you and ask if you might be getting their thing in the future)
Or, if you’d rather, you can populate the drop-down lists from the unique values of the whole table, or even from a separate longer list of all maker/model/year combinations ever in the whole world. This has the advantage or disadvantage that the customer could choose bikes whose products you don’t actually have. The disadvantage is that it makes their task harder because the droppy-downers would be longer. The advantage would be that you could find out and store for later reference what products people were looking for that you didn’t have.
(In any case it would be nice to have a table or tables of all makers, models and years, to draw from in your admin backend when you’ve got to tell the system what bikes a product is relevant for. Saves typing and typos.)
Anyway, then, whichever way you create the droppy-downer, you can take the droppy-downer selection that the customer’s given you, and use it to query the relevance_of_product_to_bikes table, so that if there’s a match, the query returns the SKU. Then you can pick out the product by its SKU and display it. (or else “We don’t have that for your bike at present, sorry!” if you’d allowed them to select something that wasn’t there.)
So that is how I would probably do it in terms of algorithm. What that looks like in code is a whole other kettle of fish! But I’m sure it’s something that a reasonably competent PHP/MySQL bod could do.
On the other hand if Magento does (or will soon) allow multiple values per attribute, then you could do a less elegant version with rather less coding, whereby each product has multiple values for an attribute called “bike”, each value being make/model/year all stuck together in a lump. You’d then have to concatenate the droppy-downers before you did the search on “bike”. But that method would be more clunky - by which I mean it would fail to play to the strengths of databases, and probably lead to more human error in populating the tables.
Hope that was some help. At least it’s a re-statement of the problem, and someone else may be able to elaborate on it, or correct me if I’m wrong. Good luck!