sql - How to design this DB structure in MySQL? -

i stuck on how implement more complicated (at least me) database structure, seems me dynamic number of columns in table.

what need create sql table/s shuttle module store many shuttles each has different number of seats , every seat should have different price.

for example:
1 row: shuttle 8 seats, seat #1 = $20, seat #2 = $15, seat #3 = $13 ... seat #8 = $7.
1 row: shuttle 16 seats, seat #1 = $25, seat #2 = $22, seat #3 = $20 ... seat #16 = $15.

the solution should handle different types of shuttles, 1 8 seats, 1 16 or other number of seats depends on number of seats admin wish add.

now, thought few methods , think both not enough.

1. first idea is create table maximum of 16 columns seats, nullable , admin inserts new shuttle numbers of seats needed , rest null. feel bad idea. let's ignore case if admin wants add more 16 seats huge drawback, how table like:

table: shuttle columns: id, name, num_of_seats, seat_1_price, seat_2_price, seat_3_price..., seat_16_price 

then row in table might this:

1, florida shuttle, 8, 20, 15, 10, 8..., 5, null, null, null 

i 100% sure approach wrong.

2. second idea is create 2 tables, 1 store shuttle detail called "shuttles" , second store shuttle_seats_price relation, example:

table: shuttles columns: id, name, num_of_seats  table2: shuttle_seats_price columns: id, shuttle_id, seat_number, seat_price 

a typical entry in structure like:

table: shuttles columns: id, name, num_of_seats row: 2, florida shuttle, 8  table 2: shuttle_seats_price columns: id, shuttle_id, seat_number, seat_price row: 1, 2, 1, 20 row: 1, 2, 2, 15 row: 1, 2, 3, 13 row: 1, 2, 4, 11 ... row: 1, 2, 8, 5 

so conclude, need create solution store different shuttles unpredictable number of seats each seat has different value of price, 1 table not seem right solution me because see it, not approach create 100 columns, each column each seat , start inflating null values.

thanks in advance! hope asked clear!

i create 2 tables: shuttle , seat.

you read this, shuttle can have multiple seats, seat connected 1 shuttle.

so create shuttle, , add amount of seats want it.

i've made uml diagram can see yourself

edit: adjusted picture

edit2: remove "rowid" attribute shuttle, mistake me.

edit3: #seats isn't required. example if want number of seats shuttle id 3 can :

select count(*) seats shuttle_id = '3'; 

the result of query amount of seats in shuttle.