I am developping an application that creates random "battles / versus" of two things that have the same type. Let's say it's about cars and their features for example:
There would be many group of features, things related to safety, to comfort, etc.
Car A would have one security feature, airbags, Car B it would be ABS and air conditioning and Car C heated seats.
Now I have to store a list of versus: airbags vs. ABS, heated seats vs. air conditioning. Note that I can't do airbags vs. heated seats.
I've come up with two ideas to make this work.
users
id | username
cars
id | name
groups
id | name
features
id | car_id | group_id | value
versus
First version:
id | user_id | group_id | car_a_id | car_b_id | winner_id
Second version:
id | user_id | feature_a_id | feature_b_id | winner_id
Now with the first version, I have to use car_a_id, car_b_id and group_id to fetch features but that ensures I am not comparing features that are not in the same group. The thing is if any feature gets deleted I'll will have an invalid versus and I won't know that until I actually fetch the features.
The second version solves that, since I can just add a ON DELETE CASCADE to my foreign keys. But now I have to make sure each feature of a row is in the same group when fetching them (I can't rely on the fact that the list of versus is actually valid).
Now I don't like either of these solutions, I feel like I'm doing something completely wrong but I can't find out anything better.
Is there a better / simpler way to do that?