MySql – need help modeling database

July 4, 2022

I’m currently working on a mobile application.

The principle is simple, a user can add a marker on a map.
The marker belongs to a category and each category has different fields.

Example :
A user adds a marker on the map, chooses the category (point of interest) then fills in the necessary information for the point of interest (Title, Description, Type).

If he wants to report an obstacle, the fields will be different (type, description)

Here is how I modeled this

My modeling

the problem is that each marker will necessarily have 2 null fields.

Do you think my modeling is consistent? If not, do you have an idea for a solution?

>Solution :

I think that you would need following tables.

markers (id, name, latitude, category_id)
categories (id, name) 
fields (id, category_id, name)
values (id, marker_id, field_id, value)

Because each marker have one category.
Each category have many fields.
Each value has 2 foreign keys because each marker has his own fields, so we need also values for them.

markers