CODEWITHSUNDEEP
sql-server
user1630359
user1630359

Reputation: 103

How to normalize database with tree structure

I need some help with SQL structure. I write articles and want to share them online in my web site, that users could comment on them.

Each article contains name, paragraphs, sections and blocks of content. You can look at it as a tree: article => paragraphs => sections => blocks of content. each article has many paragraphs, each paragraph has many sections and each section has many blocks of content.

Now, i would like that users will be able to add their comments for every level in the tree. In order to do so i created a table for each entity (article, paragraph, sections and blocks of content) that holds static information .

My question is what is the best way to handle the dynamic part, where to store the users' comments? what is the best solution here?

Thanks.

Upvotes: 1

Views: 662

Answers (2)

Juan Lago
Juan Lago

Reputation: 1048

My recommendations it to create a table per each entity that where to store the comments.

So for example you can create the following tables:

  • article_comments
  • paragraph_comments
  • section_comments
  • blockscontent_comments

so each table is going to store the parent entity Id, the user ID (If is registered or null if is not registered) and the comment.

The reason why I recommend tou to use different tables instead only one are the following ones:

  • It is a scalable solution in the sense that in the future you can add custom fields to each comments table that are only related to the parent entity. If you do that with one table in the future the table structure can become messy with many empty or null row fields.

  • Your tables are going to look sorted and structured.

  • If in the future you have a big number of comments your RDBMS will take less time querying from different tables instead of one table (And queries become heavies and slower when long text/blobs fields are used).

  • It is easy to setup foreign constraints with only one parent table and the referential integrity checks are going to take less time.

Upvotes: 2

Erik Hart
Erik Hart

Reputation: 1344

The content tables will be dynamic, too, because content can be added, modified or deleted.

I would create a single comments table with only the primary key and the comment data (text as nvarchar(max) data type, Foreign key to Author).

Then, for each content table, a reference table, containing PKs from the content and comment table as a pair of foreign keys. Theoretically, the comment could belong to multiple content items, something that should be prevented in the application (a unique constraint on the Comment FK reference could limit at least to one parent per content type).

Don't forget proper indices on the content, comment and reference tables

Upvotes: 1

Related Questions