>programming and database stuffI have an idea for a site I want to make (to learn, and to maintain as a hobby!), but I'm not sure what I should be searching for. I have some basic knowledge of some programming languages, and some other markup languages and a very faint gist of sql (mysql?).I know roughly which databases and tables I'd need to set up for my project, but I have issues searching up information on how to implement a tag-like system. As an example, imagine I want to make a database of "restaurants" in my town, and pre-approved users will be able to add their own "reviews", marking the various cuisines were to find on the menu as well as the vibes of the place (predefined "tags"). The users may also choose to make some of their tags prominent, adding extra weight when calculating its average.For example, Annie and Buford have visited the restaurant Heart of India, and would like to review it for the catalogue!>Annie checks off Indian, Vegetarian, and Spicy cuisine tags, as well as Warm, and Relaxed atmosphere. Annie marks Indian and Warm as prominent.>Buford checks off Indian, Spicy, and Relaxed. He marks Spicy as prominent.>Combining the two, Heart of India is tagged as Indian, Vegetarian, Spicy, Warm, and Relaxed, with some tags having a ... higher score? maybe? than others, partially due to being marked as prominent.Most importantly to me, how do I go about making the amount of tags a user may add variable, as well as making it simple to add new tags? I'm imagining that adding a table column that will likely be empty for each tag will be space inefficient, especially if I'm looking to add a lot more or many tags. Or is that exactly what I have to do? Just have giant tables of empty columns with some that are checked?
This is a classic problem in database schema.https://en.wikipedia.org/wiki/Many-to-many_%28data_model%29The simple solution is to make a mapping table. reviewID and tagID are foreign keys, and together they create a composite key (optional).Extrapolating beyond my high school understanding but you can then presumably make a json cache like that stores each restaurant with a (tag, prominence) list.Maybe you could make a View using a list column type?
>>1543089Oh and note the fifth tag being added. (It's highlighted green.)
