Use the supertype/subtype design pattern.
Since you wish to relate to news articles and comments in a similar way and at a higher level than which type they are, you create a supertype table. The identity value of these moves to the supertype table. Here:
Currently you have this:
NewsArticles --(and may I suggest shortening this to just Article)
- ArticleID (PK)
- Message
...
Products
- ProductID (PK)
- Title
...
Now create a supertable. This one has the identity column (if that's what you're using):
Items (come up with a better name than this)
- ItemID
- ItemTypeID (FK to a new lookup table which has, say, 1=Article and 2=Product)
- Any columns in common between NewsArticles and Products, or could be treated similarly.
- For example, Title or something similar to Title probably exists for both.
Your two original tables become subtype tables: they lose their identity columns and get an FK column called ItemID (yes, even though it is in the NewsArticle table, although if you really wanted you could call it ArticleID... I haven't yet chosen a really strong preference between the two, there are advantages to each way). This FK column is also the clustered index. Now the relationship between Item and Article is 1-to-zero-or-one. The relationship between Item and Product is also 1-to-zero-or-one. The relationship between Item and (Article UNION ALL Product) is 1-to-one.
Get the idea? The article table's primary key value is not dense any more. If ItemID 1 is an article then you have ArticleID 1. If ItemID 2 is a product then there will never ever be an ArticleID 2. It's just "missing" from the table.
When you want to use Articles and Products as similar items, you select from the Item Table. You can FK reference the item table in your database. That makes your comment table look like this:
Comments:
- CommentID (PK)
- ItemID (FK to Item table)
- Comment
If you want to use something that is just an article and no products are allowed, you select from the article table. You can FK reference the article table instead of the item table, and then be sure you won't get any products in there.
You can add as many subtypes as you need for things that need to be related to in the same way. Each one gets its own subtype.
This is a common design pattern for databases that have to interact with companies and people in similar ways. They both have a taxpayer identification number (called SSN for the person). They both have birth/start dates, death/end dates. They both have addresses and phone numbers and can enter into contracts and be invoiced and pay bills and get awards, whatever.
So they make a supertype Party table. Things that can be made common between the types go into this table. Many designers choose to use RestOfName and FirstName. For companies, the first name is left blank. TIN/SSN is in this table.
When one needs to do anything that can be a person or a company, the selection or FK is made using the Party table/PartyID. If only a person is allowed, it uses an FK to the Person table.
It is a matter of personal preference whether you label the column in the person table PersonID or PartyID. I lean toward PartyID because of the confusion that can arise not knowing where PersonID originally comes from. I find the potential confusion about PartyID-FK to the person table vs. the party table less problematic.
That is, I find it better to make it clear by the use of "PartyID" in the Person table that this value does not originate here, rather than for someone later to come to the table and not even realize that the column is in fact a foreign key as well as a primary key. Naming it PersonID, while making it easier in FK tables that reference it to instantly know that one is restricting this column to only people, hides that there is anything else to know. Better to have a hint that there is something one must look up (what table does this FK to?) than fail to give notice that there is anything to look up at all and REALLY confuse someone. Another advantage is that if it comes time to allow more subtypes, the column name is already appropriate (like in your Comments table if you had called it ArticleID then wanted to expand it to allow Products as well, now it's problematic to change it to ItemID).
