1
\$\begingroup\$

I'm scraping posts from various discussion forums and storing those posts, along with some metadata, into a relational database (SQL). My actual use case is a bit more complex, but I think I can give you the idea by using just two fictitious forums, one focusing on fishing ("Fish Forum") and another focusing on golf ("Golf Forum"). In reality, I would also be storing threads, user info and other things you can imagine, but let me only focus on the actual posts, i.e., messages written by the users.

I have a bunch of tables per forum, differing slightly from each other depending on the forum structure. On the fishing forum, each post has an id, timestamp, reply count, information on which post a particular post is replying to and a scrape time. The golfing forum is similar, but the metadata differs a bit. The main point is that both forums have some internal post numbering scheme assigning unique identifiers to each post.

Now, my spider is working well and storing data, but I'm also interested in passing those messages through a natural language processing (NLP) model which is predicting a category for each post. For this, I've set up a third table called PREPROCESSED_POSTS. Here, we store the post id, the source forum (in this case, "fish" or "golf"), the predicted category (e.g., "talks about the weather"), confidence (e.g., "0.952") and the identifier of the NLP model (e.g., "my_latest_nlp_model").

CREATE TABLE FISH_FORUM_POSTS (
    id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,

    post_id INT NOT NULL,
    created_at DATETIME2 NOT NULL,
    msg NVARCHAR(MAX) NOT NULL,
    reply_count INT NOT NULL,
    reply_to_post_number INT,
    scrape_time DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT UNIQ_FISH_POST UNIQUE(post_id)
);

CREATE TABLE GOLF_FORUM_POSTS (
    id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,

    post_id INT NOT NULL,
    profile_id INT NOT NULL,
    msg NVARCHAR(MAX) NOT NULL,
    created_at DATETIME2 NOT NULL,
    like_count INT NOT NULL,
    scrape_time DATETIME2 NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT UNIQ_GOLF_POST UNIQUE(post_id)
);

CREATE TABLE PREPROCESSED_POSTS (
    id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  
    post_id INT NOT NULL,
    source_forum NVARCHAR(256) NOT NULL,
    prediction NVARCHAR(256),
    confidence DECIMAL(12, 4),
    model_name NVARCHAR(256)
  
    -- Each post on a forum has at most one prediction by any given model
    CONSTRAINT UNIQ_PREPROCESSED_POST UNIQUE(post_id, source_forum, model_name)
);

What annoys me here is that PREPROCESSED_POSTS just stores post identifier and the forum, so it's possible that by mistake we enter a post id and/or a forum that doesn't exist (e.g., "cars"). So I would want there to be an actual reference or a foreign key to something that exists. The problem is that I can't seem to do that because the posts are in two different tables.

This is probably a well-known problem. What design should I apply here to circumvent this problem?

I guess if I build a view that would distill the essence (= post_id plus forum_name) would be OK, but I probably can't make a foreign key into a view. It also seems difficult to use just one table that could then be referenced in PREPROCESSED_POSTS, because in reality all forums I'm scraping are very different with different kind of metadata. Also, I could add a check to enforce source_forum is either "fish" or "golf", but that only solves the problem partially as it doesn't address the post_id being valid.

\$\endgroup\$
1
\$\begingroup\$

If the post_id is unique, why not make it a primary key? Sometimes it is justified to have an internally-controlled primary key like id but here the justification is unclear. One catch is that if the two forums are from different websites, their ID sequences will be different and you don't have a choice but to maintain your own primary keys.

Transact-SQL supports column-level unique constraints, which means - so long as you don't care to name your constraint - you can delete your table-level constraint syntax and move your unique to the column.

reply_to_post_number looks like it should be a foreign key, if you are able to assume that all linked posts are present in the database. Note that Transact SQL doesn't support restrict for whatever reason, and you're limited to disabling relational integrity for this foreign key to prevent cascade cycles.

reply_count should have a check constraint to be non-negative. Depending on what confidence is, you may be able to write a check constraint to ensure that it's between 0 and 1 for instance.

preprocessed_posts.post_id should certainly be a foreign key, but I'm going to propose that that column be deleted altogether and id made to be both a primary and foreign key.

A way to cut out a large amount of redundancy between your fish and golf tables is to make a third table, FORUM_POSTS, containing only the common columns; have FISH_FORUM_POSTS and GOLF_FORUM_POSTS only contain varying columns; and have the latter two include an ID column that is both a primary key and a foreign key to FORUM_POSTS. Conceptually this is analogous to class inheritance in the OOP world. This would allow you to have a foreign key from PREPROCESSED_POSTS straight to FORUM_POSTS.

it's possible that by mistake we enter a post id and/or a forum that doesn't exist (e.g., "cars")

This is what foreign keys and relational integrity are for. However, to prevent redundancy, I don't think that the source forum column should exist at all. The source forum would be implied by the presence of a matching row in either of the fish or golf tables.

if I build a view that would distill the essence (= post_id plus forum_name) would be OK

This does not sound like the right way to go. Nothing I've seen here calls for a view; just a plain-old normalized relational schema.

Proposed

Runs fine on dbfiddle:

create table forum_posts(
    id int identity primary key,
    created_at datetime2 not null,
    msg nvarchar(max) not null,
    scrape_time datetime2 not null default current_timestamp
);

create table fish_forum_posts(
    id int primary key references forum_posts(id)
        on update cascade on delete cascade,
        
    -- Delete this if it's in the same sequence as the other fora
    post_id int unique not null,
    
    reply_count int not null check(reply_count >= 0),
    
    reply_to_post_number int references fish_forum_posts(post_id)
        -- Needed to avoid cascade cycles
        on update no action on delete no action
);

create table golf_forum_posts(
    id int primary key references forum_posts(id)
        on update cascade on delete cascade,
        
    -- Delete this if it's in the same sequence as the other fora
    post_id int unique not null,
    
    profile_id int not null,
    
    like_count int not null check(like_count >= 0)
);

create table models(
    id int identity primary key,
    name nvarchar(256) unique not null
);

create table preprocessed_posts(
    id int not null references forum_posts(id)
        on update cascade on delete cascade,
        
    prediction nvarchar(256) not null,
    confidence decimal(12, 4) not null check (confidence between 0 and 1),
    model int not null references models(id)
        on update cascade on delete cascade,
        
    primary key(id, model)
);
\$\endgroup\$
2
  • \$\begingroup\$ I wonder how the subtypes of the posts should be detected? So if I want all golf posts, I can select * and inner join them with forum posts. But in my end application, the user is exposed to all posts and I want her to be able to filter on a source forum (golf, fishing). How is this best supported? One option is to give a view to the application that makes selects over golf, fish etc. posts and adds one constant source column. Or should the subtypes know their type (e.g., a source column)? Ofc, I'd do this in a normalized manner by using a separate table they link to which acts like an enum. \$\endgroup\$
    – Dan Tony
    Oct 6 at 16:34
  • \$\begingroup\$ Let's discuss in chat.stackexchange.com/rooms/130294/… \$\endgroup\$
    – Reinderien
    Oct 6 at 16:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.