sql - mysql: most efficient method to store parameters with unknown type (string/int) to the db -

i have messaging system contains default templates. example:

you won x coins

friend x wants share url y you

so number of parameters varies in each message/template type.

i thought of 4 types of solutions resolve issue:

1. creating parameters table , represent each parameter type string. problem here user_ids represented strings joins slower. e

2. creating parameters table each row contains following boolean tells if parameter int or string, , 2 other columns 1 type text , other type int. here we're kind of wasting space because whenever use int parameter row contains unused string cell.

3. not create parameters table @ all, each notification contains parameters string contains each needed parameter seperator ;. 1 may slowest solution.

4. creating 2 different tables each time of parameters: notification_param_int , notification_param_string , table contains each parameter in notification relevant table. solution may slower solution 2 because need check table fetch information first.

are there other options did not think ? if don't care space, speed, method should take ?

i'm not mysql expert conclusion of each method may wrong.

any information appreciated.

thanks you!

often, when having open-ended property table describe, you'll discover of properties more important (either need there, or need fast). in case, promote fields in main record.

i imagine uid being that. problem later if needed have list of uid (friends), promote friends own table.

in case, i'd opt #1 or #2 , think promoting important properties if need arises.