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
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?
CodePudding user response：
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
| id | name | category_id |
|---|---|---|
| 1 | new-york | 7 |
categories
| id | name |
|---|---|
| 7 | POI |
fields
| id | name | category_id |
|---|---|---|
| 1 | Title | 7 |
| 2 | Description | 7 |
| 3 | Type | 7 |
values
| id | marker_id | field_id | value |
|---|---|---|---|
| 1 | 1 | 1 | title of |
| 2 | 1 | 2 | this is description for the marker new-york |
| 3 | 1 | 3 | regular |
CodePudding user response：
If Hunters, POIs, and Obstacles are never reused, it seems quite wasteful to have separate tables.
markers (id, name, latitude, longitude, type, description,
title -- optional,
radius -- only for type='Hunter'
)
Yes, I still have two NULLable columns. But they are more logical here than in the hierarchy design you had.
Do you ever need to iterate over "all Hunters"? Probably it would be fine to scan this unified table WHERE type='Hunter'.
SQL, especially MySQL, does not play well with hierarchical structure; try to avoid it.