my team and I are working on an application server for a forum site and try to setup a good SQL architecture so our project does not run into scaling problems. As we are no experts on this field and have not found much on the web about similar issues I decided to ask for your help on this site.
Setting up a forum database in SQL seems straight forward as you could organize it with tables "users, threads, posts, comments" but for our project we do not want to have any classic sub-forums or threads but only "tags". Users create posts which can be assigned with tags so only users who also have these tags assigned can discuss this issue. Tags can be citizenship, gender,... Our app should be able to display the user (who as also tags assigned to themself) all posts with which have his/her tags or any partial accordance. There are independent tags with no relation to other tags but there can also be tags which inherit from others (example: if you have tag 'canadian citizen' you automatically have the tag 'american citizen'.
How could a SQL database handle all this sorting by any combination of tags? Maybe this is even similar to this page here. Which tables do you suggest?
Our team would very much appreciate any hint from your side.
EDIT: Our current attempt would be to create a table with pk post_id and a column for each tag storing a boolean (whether post has this tag or not) and then querying via this helper-table. Is this the right direction or completely noob-like?