-2

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?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Houry Tennessee
  • 3
  • 2
  • Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jul 04 '22 at 12:56

2 Answers2

0

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
Ergest Basha
  • 7,870
  • 4
  • 8
  • 28
IT goldman
  • 14,885
  • 2
  • 14
  • 28
  • Yes your solution corresponds, on the other hand I find it complex compared to the small problem. Also I'm not used to this kind of exercise, the complexity may come from there. – Houry Tennessee Jul 04 '22 at 12:48
  • Indeed it is exactly that! I understand better with the table view. Thank you ! – Houry Tennessee Jul 04 '22 at 12:58
  • Great. I started with the obvious entities, which are `markers`, `categories` and `fields` (just like *you* described them!). Then the `values` table is not so trivial but is needed. – IT goldman Jul 04 '22 at 13:20
0

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Yes, your answer seems to be correct as well. And if I can avoid complicated relationships, that's perfect! Thank you for your reply . – Houry Tennessee Jul 05 '22 at 12:19