Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dmiller
Starting Member
6 Posts |
 Posted - 2011-12-24 : 02:02:29
|
| Hi all, You may have seen my other post question regarding this crazy legislation tracking system I am building. While I am kicking my questions out there(because I am tired of bouncing them around in my head without finding a clear answer), I wonder what you folks think about how I should structure my key system. In short the tables work as such:main table has the basics on every bill that hits congress: say hr 100 of 2010. This links to a table with bill texts (1 to 1), major events (1 to 1), amendments (1 to many), votes (1 to many) and a couple of other things I forget right now. I estimate about 50,000 to 80,000 measures over the 10-15 years I am grabbing. I can either use, as my primary key in the main table and the foreign key in other tables a surrogate auto inc key or a composite natural key, such as: year prefix number, such as 2010HR100. These are naturally unique. Sometimes I need to account for, like the first special session of 2010, and encode that as 2010.1HR100. What do you folks think?PS I don't mean to spark a natural key/surrogate key flame war! Thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-24 : 03:04:22
|
| You can go for either of them the advantage of going with surrogate key which is autoincrementing is its self maintainable and will be generated automatically upon data population. But concern here will be it will have very little business relevanceThe advantage of using composite concatenated value is its reelevant to business but the effort for generating it has to be via manual procedure or by means of computed column in table definition. Also you need to make sure if ever your rule changes you reflect it in the computation also to ensure the uniqueness of value generated------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
dmiller
Starting Member
6 Posts |
Posted - 2011-12-24 : 03:40:14
|
| I think you are right. Having read buckets on the war between natural/synthetic keys, it seems that one of the big points against natural keys is that they might someday, somehow change. In this case, the unique key generated by the year, prefix and number will not change. House Bill 100 of 2010 will always be the same - there is no ambiguity and, though the total length of the key is variable (2010HB1, 1999.2SB2000, etc), once set these identifiers are laid out in stone (or at least, the official Senate and House Journals, which is almost as good).So really the question is - is the potential performance hit worth the extra readability? As another consideration, I would be keeping the bill id as a unique index, though not a primary if i use auto inc.I think that even the most ardent synth acolytes would consider using natural keys in this case. Tell me if I am wrong.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-24 : 04:14:03
|
quote:
Originally posted by dmiller
I think you are right. Having read buckets on the war between natural/synthetic keys, it seems that one of the big points against natural keys is that they might someday, somehow change. In this case, the unique key generated by the year, prefix and number will not change. House Bill 100 of 2010 will always be the same - there is no ambiguity and, though the total length of the key is variable (2010HB1, 1999.2SB2000, etc), once set these identifiers are laid out in stone (or at least, the official Senate and House Journals, which is almost as good).So really the question is - is the potential performance hit worth the extra readability? As another consideration, I would be keeping the bill id as a unique index, though not a primary if i use auto inc.I think that even the most ardent synth acolytes would consider using natural keys in this case. Tell me if I am wrong..
We use natural keys itself wherever possible especially in our transactional systems. The surrogate key approach is used in cases like data warehouses where we cant guarantee uniqueness of business keys due to multiple systems involved, historical data storage etc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dmiller
Starting Member
6 Posts |
Posted - 2011-12-24 : 13:29:06
|
| So do you think the performance hit will be negligible? Is there a way to benchmark a hypothetical like this? |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2011-12-26 : 14:35:37
|
| I don't think this is relevant in terms of performance.A concatenated key (one column) I think is not a good idea. But you can use a composite key (i.e. more than one column).We use Identity column for things that get entered on a regular basis - Claims in our case so Bills would be an example for yours. Tables that are pretty stable and are pretty small we use Natural Keys. A state would be a good example, life is easier to Represent New York as NY rather than 1029. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-27 : 02:53:46
|
| yeah thats trueA concatenated key can affect performance in case of joins involving large datasets. So composite key would be better option there.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Additional legislation/voter question re keys