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.
