In my system I have temporary entities that are created based on rules stored in my database, and the entities are not persisted.

Now, I need is to store information about these entities, and because they are created based on rules and are not stored, they have no ID.

I came up with a formula to generate an ID for these temp entities based on the rule that was used to generate them: id = rule id + "-" + entity index in the rule. This formula generates unique strings of the form 164-3, 123-0, 432-2, etc...

My question is how should I build my table (regarding primary key and clustered index) when my keys have no relation or order? Keep in mind that I will only (99.9% of the time) query the table using the id mentioned above.

Options I thought about after much reading, but don't have the knowledge to determine which is better:

1) primary key on a varchar column with clustered index. -According to various sources, this would be bad because of fragmentation and the wideness of the key. Also their format is pretty weird for sorting.

2) primary key on varchar column without clustered index (heap table). -Also a bad idea according to various sources due to indexing and fragmentation issues.

3) identity int column with clustered index, and a varchar column as primary key with unique index. -Can't really see the benefit of the surogate key here since it would mainly help with range queries and ordering and I would never query the table based on this key because it would be unknown at all times.

4) 2 columns composite key: rule id + rule index columns. Now I don't have strings but I have two columns that will be copied to FKs and non clustered indexes. Also I'm not sure what indexes I would use in this case.

Can anybody shine a light here? Any help is appreciated.

--Edit

Here is what I think I'll be using after reading the comments, and the rationale:

clustered identity int Id as PK
non-clustered unique index on (rule id, index)

Rationale:
1 - I will often query the table by rule id only, which is why it is the leftmost index column;
2 - The Id column can be used for subsequent operations after the first query by rule id (the id will be stored client side);
3 - I will never query by index only;
4 - The unique index guarantees insert consistency, though it still slower than a single int;

  • Does ruleID mean anything? You have half of your problem solved. No reason to trouble the Optimizer by combining values that have no meaning except for a user I would say get a good idea of how much your data is split between rules and allocate accordingly. Page splitting is another strategy that can help stave off fragmentation. – clifton_h Aug 10 '17 at 4:27
  • 1
    Why don't you define (rule_id, entity_index) as the primary key? The rule_id + '-' + entity_index can still be defined as a computed column, if it is needed for display and it won't occupy any space. – ypercubeᵀᴹ Aug 10 '17 at 5:55
  • @clifton_h Rule_Id does mean something; however, each rule can have an unlimited number of indexes, e.g. 123-0, 123-1, 123-2, etc... it is a 1-to-many. – victor Aug 10 '17 at 11:50
  • @ypercubeᵀᴹ I'm thikning about using the composite primary key. But what is the best order (rule id, index) or (index, rule id)? And how would my index on that look like? – victor Aug 10 '17 at 12:01
  • How about non-clustered identity int Id as PK, clustered for your rule_id? – Wendy Aug 10 '17 at 15:24

I'm thinking clustered pk (rule_id, index)

Do that. This isn't really a hard case. (rule_id, index) uniquely identifies a row, so that should be your clustered PK, unless there's some compelling reason to use a different design. and because

I will often query the table by rule id only

rule_id should be the leading column in the index. This supports efficient lookup by rule_id and ensures locality for inserting multiple rows for the same rule_id.

  • My first problem with this is that I rarely use rule_id and index, which means further sorting by a second column is not very useful, since most of the queries are select all by rule_id; still on this topic, I can't really do a WHERE_IN using tuples, such as where (rule,index) in ((1,2), (1,3)).... This means I need a third column with a string representation (rule_id + "-" + index) of the key, and index that column for such searches. The second problem is fragmentation, which wouldn't happen with a surogate key. So, what I'm thinking is surogate + index(rule,index) + index(string col). – victor Aug 11 '17 at 13:41
  • This way, the surogate avoids the insert overhead and fragmentation; the index on (rule, index) still keeps a sorted table for searches by rule, and the string column is "sort-friendly", which helps the index too. Can you comment on these points? – victor Aug 11 '17 at 13:43
  • 1) The compound index is optimal for queries by rule_id. 2) You can write the query differently. You don't need a third column. 3) Fragmentation won't be a big issue. 4) I still think you should just use (rule_id,index). – David Browne - Microsoft Aug 11 '17 at 14:20

Design in mind for your database. It does not matter if the database side makes sense to the app side. This theory follows the Star Schema design and includes concepts such as Durable Keys. However, you can certainly mix and match as you need. :)

[VERSION: 1.1]

  • May be Community Edited
  • Restructured format for easier understanding
  • provided examples for design

Rules:

  • Heaps will not be feared, but mastered by design.
  • Natural keys are to be shunned. For the database keys need not make any sense to the app/user side.
  • Any primary/unique keys based on a GUID or string are to be considered short-term at best.
    1. They will only result in wasted resources and wasting time reinventing what could have been solved beforehand.
    2. They furthermore are likely to be sneaky, despicable natural keys which weaken security and defeat the purpose of relational databases.
  • You must have defined your current scope of concurrent and proportional collections of Rules and Indexes within reason of a viable, scalable design.

The State of Design: Towards this aim, we know we have numerous entity_index entities that appear and disappear. We assume in this design they have some relation to Rule_ID and may even have a categorical comparison that can be exploited now or later. Whether or not they are technically in "numeric" order is irrelevant since this is the database.

Furthermore, we know conceptually of three concepts: Rules, Entity_Indexes, and the mapping mechanism that makes the design more durable to change. ESPECIALLY TO THE OPTIMIZER. It cannot be stated enough this design should server the database first.

Example of Concept:

CREATE TABLE Rules_DIM
(
    Rule_ID         INT
  , Rule_Name       VARCHAR(255)
  , <Details>
)
GO
/*
EntityIndex Key is not related to GUID that identifies this key
It helps identifiy like RuleID's and their EntityIndexes
It can and SHOULD be limited in number to an INT
  Since this is the database key
  Unless you have determined Rule_ID can be mapped to more than
  5 billion Entity_Indexes CONCURRENTLY
*/
CREATE TABLE EntityIndex_DIM
(
    EntityIndex_ID  INT -- database_key.
    EntityIndex_GUID    VARCHAR(<some_length>)
     /*Can be an actual GUID or what maps to the APP*/
  , <Details>
)
/*Our Mapping Table*/
CREATE TABLE Rule_Mapping
(
    RuleID
  , EntityIndex_ID
  , Create_Date
  , <some other Mapping column that is not related to the EntityIndex_DIM table
)
  • So you mean a table [Id][RuleId][RuleIndex] (facts) and then a table where the [Id] of this fact is the key of a row in my details table (dimension)? Is that correct? If so, the problems still exists because I will still have to query based on the RuleId and RuleIndex. Why use this instead of composite primary key (ruleid, ruleindex)? What's the benefit? – victor Aug 10 '17 at 12:00
  • No, you inserted the ID column in this example and destroyed the point of the fact table. The FACT table does not need an ID. SELECT DETAILS FROM RULEID_MAPPING A INNER JOIN ENTITY_INDEX_DIM B ON A.ENTITY_INDEX = B.ENTITY_INDEX AND A.RULE_ID = B.RULE_ID; here RULE_ID is treated as a durable key, or a detail. There is no ID column. Whatever method works, just get away from a traditional ID column – clifton_h Aug 10 '17 at 12:06
  • Sorry, I'm not really familiar with this design. Can you exemplify how the tables would look like? I'm failing to see how I would ultimately query for data using only rule id and index. – victor Aug 10 '17 at 12:09
  • I can add an example later. I am on my phone. As you say, pointers? – clifton_h Aug 10 '17 at 12:13

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.