Asking for suggestions for our SQL database/table architecture

1

Hello everyone,

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?

2

Suggest you review the Stackoverflow database, the schema is published and you can download copies with actual data. It's a proven scalable design, and has all or most of the features you're looking for. I don't know what licensing is in place for the DB schema though, if you are planning on using it.

3 Likes
3

Thank you I will definateley take a look at stack overlow.

4

Also, do proper data modeling. That is, do NOT start with "tables" and "columns", start with entities and attributes. Spend some serious time making a list of all the data elements you need. Again, WITHOUT putting them into tables yet, just get a list of all the actual data you need. Then follow the normalization process: 1NF, 2NF, 3NF, BCNF.

ONLY after that, and reviewing the data needs again, should you convert the logical design to physical tables.