I understand the second solution as not applicable as it does not offer one (object)-to-many (alert) relationship.
You are stuck to just two solutions because of the strict 3NF compliance.
I would design a lesser coupling schema:
CREATE TABLE Product (ProductID int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News (NewsID int identity(1,1) not null, ...)
CREATE TABLE Alert (
-- See (1)
-- AlertID int identity(1,1) not null,
AlertClass char(1) not null, -- 'P' - Product, 'C' - Customer, 'N' - News
ForeignKey int not null,
CreateUTC datetime2(7) not null,
-- See (2)
CONSTRAINT PK_Alert Primary Key CLUSTERED (AlertClass, ForeignKey)
)
-- (1) you don't need to specify an ID 'just because'. If it's meaningless, just don't.
-- (2) I do believe in composite keys
Or, if integrity relationship shall be mandatory, I might design:
CREATE TABLE Product (ProductID int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News (NewsID int identity(1,1) not null, ...)
CREATE TABLE Alert (
AlertID int identity(1,1) not null,
AlertClass char(1) not null, /* 'P' - Product, 'C' - Customer, 'N' - News */
CreateUTC datetime2(7) not null,
CONSTRAINT PK_Alert Primary Key CLUSTERED (AlertID)
)
CREATE TABLE AlertProduct (AlertID..., ProductID..., CONSTRAINT FK_AlertProduct_X_Product(ProductID) REFERENCES Product)
CREATE TABLE AlertCustomer (AlertID..., CustomerID..., CONSTRAINT FK_AlertCustomer_X_Customer(CustomerID) REFERENCES Customer)
CREATE TABLE AlertNews (AlertID..., NewsID..., CONSTRAINT FK_AlertNews_X_News(NewsID) REFERENCES News)
Anyway...
Three valid solutions plus another to be considered for many (objects)-to-one (alert) relationships...
These presented, what's the moral?
They differ subtly, and weight the same on the criterias:
- performance on insertions and updatings
- complexity on querying
- storage space
So, choose that comfier to you.
Another thing to consider in deciding to split the tables or not is the width of the table if you put them all in one table. Many databases will allow you to define a table where the total length of all the fields is wider than the total record length allowed. You cannot however, put data into a record that would exceed the width. Therefore, if you are going over the limit, it might be wise to split the table. Further, most databases can store and retrieve data more efficiently in tables that are less wide. So if the fields you want to split out are not always going to be queried with the fields in the orginal table and the table will be large, it might be in your best interests to split the tables out.
Another thing to consider is if you are in a 1-1 relationship now, is it likely or possible that you will want to change to 1-many later? For instance suppose the fields you want to move are out are address fields. Maybe now you only need one address, but having them in a separate table makes it much easier later to have multiple addresses.
If you split the tables, make sure you define a unique index on the FK to preserve the one-to-one relationship.
Best Answer
You have a common problem, which is how to implement table inheritance into a relational database. There is not perfect solution, as it will be fully dependent on how you are going to use your database afterwards and what you want to optimize for.
Typical way to implement your solution is one of 3 ways: create 4 separate tables, create a single table, or create 5 tables, one for the "parent" relationship and 4 for the "children". You can find more discussion on the advantages or disadvantages of each solution on:
Because the question is too general, only a general answer can be answered -optimize for the most common kind of operations you are going to be performed later; or for the restrictions you may have (simplicity of the code, minimize data size on disk, minimize CPU usage, etc.), some of which will depend on the specific dataset you have and the database storage engine you are using.