Mysql -> what is the best dinamic column soulution for advertisement webpage?

Question

I'm developing website in which will be categorized advertisements. In each category will be possible different fields of input (example: for car there will be motor size, for cat there will be a race). So I'm thinking how to build database to manage this (I will use MYSQL database). One way you can see in attached picture, I know that also is solution to create table for each values datatape, but I'm wondering that it will slow down a website. This solution which is in picture will generate empty fields in sp_advertisement_value table what isn't good also. What is in your opinion the best solution? Maybe there is something else? p.s. I'm new youser and for me isn't allowed post a pictures, so here is a link to database maket: http://www.sportsguru.eu/images/Advertisment_DB.png

Answer 1

You can store it like name/value pairs (more or less same to what you is described in the image you attached).

A simple schema would be a table having two columns name and value. Instead of having a column for each data type like value_int, value_string etc. have one single column value who's data type can be varchar (or Text as seems fit to you). You can do all the data conversion in your application code as per your needs.

You can do some normalization here too for instance instead of saving name you can make a separate lookup table named parameters having id, name and other related information and have the parameter_id in the table where you are storing parameter values.