If you are filtering by the middle of a string and that is the only (or the primary) filter in your query, then you are at best going to see an index scan and if there is no index on that column a full table scan. This is usually not desirable.
If all those properties are stored in columns within the cars table then querying
FROM cars WHERE maker='opel' AND date_of_manufacture BETWEEN '1997-01-01' AND '1997-12-31' would likely be much more efficient if there is an index over
maker, date_of_manufacture or
date_of_manufacture, maker than looking for
WHERE manufactured_id LIKE '%-open-1997-%' as the query runner will be able to seek to the first match (an operation of complexity log(n) so for a million records it would need to compare ~30 index blocks before finding the right place) then scan down the matches and lookup the rest of the columns from the heap/cluster. With the
MATCH approach looking at the middle of a string it would have to compare all the million strings against your match criteria (1,000,000 comparisons rather than ~30) - even if the whole index is in memory at the time that is going to consume noticeable extra CPU time and if it is not in memory at that point there will be the extra IO hit to consider too.
You question is a little unclear on what you are considering the alternate structures to be (I don't see why JOINs necessarily need to be involved at all) so I may be missing your point. It would be useful to add to the question a diagram or other more detailed indication of the table structures you are considering.