Should I use a surrogate key or use the natural keys?
I am building a list type to let users make lists of various things in my system. The list elements therefore need references to both the type and identifier of the things they will be pointing at.
For instance, I will allow users to make lists of products. The list elements will therefore need to know that they are holding products (so look in the p开发者_如何学JAVAroducts table), and will need the product ID.
Some of the things lists will be storing will have non-numeric identifiers.
So, should I put my id/type pair in the list element, or should I create a new table that creates a surrogate key for the id/type pair as a simple int and use that...?
The surrogate key will take an extra lookup/join to get the key pair, but I won't need to mash two columns into my list elements table.
I'm not sure if you're planning on doing any kind of statistics or reporting, but I've always found it useful to keep primary keys to just one column. I always create an auto-incrementing integer as the pk. The tables should be a storage area for just "data". The simpler the better. There's no telling what you may want to do with the application as it grows, so keeping things simple will allow you more flexibility as time goes on.
精彩评论