September 30, 2021 at 11:12 pm
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? 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?
October 1, 2021 at 4:14 am
Well a lot could said about this topic. The classic threaded forum/subforum organization might seem straightforward it depends on many things. If by "threaded" it means "nested html" then that's pretty straightforward yes. What you're describing tho sounds similar to a graph design. SQL Server has built-in features to support node/edge type data models
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
October 1, 2021 at 1:35 pm
I'm thinking about this rather differently. My perception of a system that is based almost entirely on a hierarchy of tags seems like it's mostly a matter of time before it becomes entirely unworkable due to the load created by constant growth to both the hierarchy and the data, and I'm not convinced that it's a sustainable data model. What becomes your primary key on a table containing the posts? And how much indexing can you do? What is the plan for a column or columns to hold the tags?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 1, 2021 at 4:18 pm
Start with data modeling first, so not with "tables"/"columns" but with entities and attributes. Spend some time getting a full list of all data attributes (elements, "columns") that you need to capture and store. Then go thru the normalization process: 1NF, 2NF, etc.. Far, far too many people try to skip those steps, which is a terrible idea. You need to spend some time working on the data model before locking yourself into physical structures.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply