vendredi 24 février 2023

Record with 'all' in database

I have the following hypothetical scenario:

A user can toggle aircraft categories wether he is interested or not. If the user toggles Helicoper the following checkboxes with sub-categories will appear:

'Chinook', 'Apache', 'Cargo', 'Military'

to which the user can select from in form of checkboxes.

If the user toggles Airplane the following checkboxes with sub-categories will appear:

'Passenger', 'Ultralight', 'Cessna', 'Cargo', 'Military'

The following tables are designed to store the user's (id: 1) options:

aircraft_type

id | name   
1    Helicopter
2    Airplane

aircraft_class

id |  name   
1     Chinook
2     Apache
3     Ultralight
4     Cessna
5     Cargo
6     Military 

aircraft_x_class

id | aircraft_id  |  aircraft_class_id
1       1     |        1  
2       1     |        2
2       1     |        5
2       1     |        6
3       2     |        3
4       2     |        4
5       2     |        5
6       2     |        6

user_x_aircraft_x_type_x_class

id | user_id | Aircraft_x_class_id
1      1           1 
2      1           2
3      1           3

Now if I happen to have 1000 users who all selected every aircraft_class then the records pile up quickly.

To prevent (perhaps) unnecesseary records in my joint-tables I have thought of the following option at aircraft_model which is 'all'.

aircraft_class

id |  name 
....  
7     all

aircraft_x_class

id | aircraft_id  |  aircraft_class_id
....
7       1     |        7 
8       2     |        7

So when a user selects every aircraft_class of a certain type, it will not create all the records, but instead just one that states 'all'

user_x_aircraft_x_type_x_class

id | user_id | Aircraft_x_class_id
1      1           7

Question

Is this a common/good thing to do to reduce records at nested options? Are there perhaps better ways?




Aucun commentaire:

Enregistrer un commentaire