I have to store records in a "Analyze" table which are referenced to a Object with a defined Id (nvarchar20).
I came up with two possible designs (see Option A or B) for the Analyze Table:
What I'm not sure about is weather its better to store the primary key of the different objects (ObjectA, ObjectB, ...) in separate columns or simply store the plain ObjectId
The Analyze Table is growing very fast and most operations are read via a given ObjectId. So most cases you have a ObjectId and have to search the Analyze Table.
The pattern of the ObjectId is always the same: for example you can identify a given ObjectIds Type if you look at the eight to fourth last characters CA16834K23850001ABCD
0001 is always ObjectA
0002 is always ObjectB
ObjectA
| PK (bigInt) | ObjectId (nvarchar20) | otherfields |
| ------------| --------------------- | ------------|
| 1 | CA16834K23850001ABCD | .. |
| 2 | CA16834K23850001ABCE | .. |
ObjectB
| PK (bigInt) | ObjectId (nvarchar20) | otherfields |
| ----------- | --------------------- | ----------- |
| 1 | CA16834K23850002ABCD | .. |
| 2 | CA16834K23850002ABCE | .. |
Option A: AnalyzeTable
| id (bigInt) | ObjA_PK (bigInt)| ObjB_PK (bigInt)| otherfields... |
| ----------- | --------------- | --------------- | -------------- |
| 1 | 1 | NULL | ... |
| 2 | NULL | 1 | ... |
Option B: AnalyzeTable
| id (bigInt) | ObjectId (nvarchar20) | otherfields... |
| ----------- | --------------------------- | --------------- |
| 1 | CA16834K23850001ABCD | ... |
| 2 | CA16834K23850002ABCD | ... |
What is the better design for reading the AnalyzeTable. Since numeric indexes might be faster than an index on an nvarchar?