There is a database with spare parts for some machinery in a factory. Within the spares listed, there are some parts that could be alternatives to each other. For example, let's say on machine A there is a bolt 1 which can be used in machine B in place of bolt 2.
How can we link the alternative spares and through which SQL statement could list all possible alternatives to a specific spare part?
If you are after of the design then below should suffice
| machine_master_table |
machine_id
machine_name
....
|machine_parts_table
id
machine_id
parts_id
| master_parts_table | -- all parts are listed here.
parts_id
parts_name
parts_type
size
blah...
| alternate_parts_table | -- this table will link all your main part and alternates
id -- identity seeded
main_parts_id
alts_parts_id
date_effective -- if you want to keep historical alternate
end_date