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 (=
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.