The safest way to do this is to enforce your business rules using the built-in referential integrity constraints, as Alexander Kuznetsov describes in his article, "Storing intervals of time with no overlaps".
Applying the techniques listed there to your sample table results in the following script:
CREATE TABLE [Z_STORE_TEAM](
[STORENUM] [int] NOT NULL,
[TEAM] [varchar](10) NULL,
[EFFECTIVE] [date] NOT NULL,
[FINISHED] [date] NULL,
PRIMARY KEY CLUSTERED
(
[STORENUM] ASC,
[EFFECTIVE] ASC
)
) ON [PRIMARY];
INSERT [Z_STORE_TEAM]
([STORENUM], [TEAM], [EFFECTIVE], [FINISHED])
VALUES
(1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date)),
(1, N'2', CAST(0x81380B00 AS Date), NULL),
(2, N'1', CAST(0x01380B00 AS Date), NULL);
Modifications:
-- New column to hold the previous finish date
ALTER TABLE dbo.Z_STORE_TEAM
ADD PreviousFinished date NULL;
GO
-- Populate the previous finish date
UPDATE This
SET PreviousFinished = Previous.FINISHED
FROM dbo.Z_STORE_TEAM AS This
CROSS APPLY
(
SELECT TOP (1)
Previous.FINISHED
FROM dbo.Z_STORE_TEAM AS Previous
WHERE
Previous.STORENUM = This.STORENUM
AND Previous.FINISHED <= This.EFFECTIVE
ORDER BY
Previous.FINISHED DESC
) AS Previous;
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT UQ_STORENUM_PreviousFinished
UNIQUE (STORENUM, PreviousFinished);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT CK_PreviousFinished_NotAfter_Effective
CHECK (PreviousFinished = EFFECTIVE);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT UQ_STORENUM_FINISHED
UNIQUE (STORENUM, FINISHED);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT FK_STORENUM_PreviousFinished
FOREIGN KEY (STORENUM, PreviousFinished)
REFERENCES dbo.Z_STORE_TEAM (STORENUM, FINISHED);
GO
ALTER TABLE dbo.Z_STORE_TEAM
ADD CONSTRAINT CK_EFFECTIVE_Before_FINISHED
CHECK (EFFECTIVE < FINISHED);
An attempt to insert the fourth row of sample data now fails with an error message:
INSERT [Z_STORE_TEAM]
([STORENUM], [TEAM], [EFFECTIVE], [FINISHED])
VALUES
(2, N'2', '20140201', NULL);
Please read Alex's article to understand how these constraints ensure your table data will always be valid. Having a set of constraints enforce your data integrity means no trigger code is required.
Related article by the same author:
Modifying Contiguous Time Periods in a History Table
If your RTO is ~ 1 minute and your RPO is 1 day of data loss then you could use log shipping. This would not require AD, a Windows Cluster or Availability Groups.
If you shipped your logs every 15 minutes the recovery time needed to bring the secondary should be well under a minute (Needs testing but I would be surprised if it wasn't from what you have explained above).
The only thing you would need to deal with would be how the application manages the connection to the secondary when the primary is down. This could be handled in the application, a load balancer (with static routes that would need to be altered on failure of the primary) or a clustered name resource (would probably need to be manually failed over).
A FCI would mean you wouldn't need to handle and monitor all the log backup/copy/restores of log shipping and your RTO would be lower. This is not possible without AD. Also you can't have Availability Groups without a Windows cluster which also requires AD.
Best Answer
This is a case of "relational division". I tagged the question accordingly, you can find basic information in the tag info.
You did not provide details. Assuming a standard many-to-many (n:m) relationship between products and components which is implemented with a
product_component
table, a basic solution could look like this:This needs at least an index on
product_id
and oncomponent_id
to be fast, ideally on(component_id, product_id)
.There are many other ways. The best query depends on your exact table definition and data distribution. We assembled an arsenal of queries under this related question on SO, for Postgres / MySQL, but the SQL is (mostly) the same:
The above query finds products that contain at least the components with
component_id
1
,2
and3
. To find products that consist of those components exactly (no others components):Again, there are various ways exclude products with additional components: