Add key attributes to the Topics table

JeremyCaney · JeremyCaney · commit c50f44d03222 · 2020-12-23T13:56:34.000-08:00
`Key`, `ContentType`, and `ParentID` have historically been stored in `Attributes` as indexed attributes—but they've always been treated as exceptions. They've mostly been filtered out of `AttributeIndex`—or, more recently, at the `GetTopics` stored procedure level (ba4112d). There is a frequent need to extract them (and only them) from `Attributes` for the sake of joins. For reporting and maintenance, they've the most commonly queried fields. And, generally, not only are they not versioned, but it doesn't necessarily make sense to version them—for instance, versioning `ParentID` would cause problems since it would conflic with the actual nested set hierarchy. And, of course, there's no referential integrity for the `ParentID`. Given this, I've migrated them to be columns on the `Topics` table. This makes them first class citizens that are much easier to query and join against, and also provides better data integrity since their requirement can be enforced—as can their referential integrity in terms of `ParentID`. This just establishes the schema. Future commits will need to migrate the data, and then update the various SQL calls to reflect that change. Until those are entirely complete, this change will continue to exist in a limbo, with the data being maintained and pulled from both locations.
diff --git a/OnTopic.Data.Sql.Database/Tables/Topics.sql b/OnTopic.Data.Sql.Database/Tables/Topics.sql
@@ -9,9 +9,15 @@ TABLE	[dbo].[Topics] (
 	  [TopicID]		INT	IDENTITY (1, 1) NOT NULL,
 	  [RangeLeft]		INT	NOT NULL,
 	  [RangeRight]		INT	NOT NULL,
-  CONSTRAINT	  [PK_Topics]	PRIMARY KEY
+	  [TopicKey]		VARCHAR(128)	NOT NULL,
+	  [ContentType]		VARCHAR(128)	NOT NULL,
+	  [ParentID]		INT	NULL
+  CONSTRAINT	  [PK_Topics]		PRIMARY KEY
   CLUSTERED (     [TopicID]		ASC
-	  )
+  ),
+  CONSTRAINT	[FK_Topics_Topics]
+  FOREIGN KEY (	[ParentID] )
+  REFERENCES	[Topics]([TopicID])
 );
 
 GO
