| Author |
Topic |
|
timgaunt
Posting Yak Master
115 Posts |
 Posted - 2005-04-20 : 17:14:40
|
| Hi,I'm sorry if this has been asked before but I've done a bit of digging and I just seem to confuse myself more and more, basically I'm making a table of companies for a website, and I need to setup some sort of Primary Key/Unique field. I'm thinking the company name would be a good one to use but that's not going to be unique field so what could I use instead, some sort of algorithm based on time etc? If so what sort of algorithm could I use should I feel it necessary?Many thanks.Tim |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-04-20 : 18:02:02
|
| What else paired with a company name would make it unique?That's the question you need to ask yourself.I'd not go with time because what if the same company is created twice at nearly the same instant. It could happen....Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-04-20 : 18:23:21
|
| Another popular approach is to use an identity column as the primary key. Its still a good idea to ask yourself MichaelP's question, "What else paired with a company Name would make it unique?". Then you can use those columns to create a unique constraint (to prevent duplicate records).Be One with the OptimizerTG |
|
|
|
timgaunt
Posting Yak Master
115 Posts |
Posted - 2005-04-20 : 18:42:44
|
| Well thats what I was thinking but I've seen people generate keys ie www.ehawker.co.uk which have nothing to do with the name etc but is built from time and some sort of algorithm.Wouldn't that be better?Tim |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-20 : 18:57:05
|
| No a PK built from time using some sort of algorithm would not be better. If you've got nothing that makes the row unique, then just use an identity column. It will be faster to insert the row than to run some sort of algorithm to generate the key.Tara |
|
|
|
timgaunt
Posting Yak Master
115 Posts |
Posted - 2005-04-20 : 19:01:56
|
| Ok, thanks, just out of interest is that the same if the key were being generated in the web code then? Are there any draw backs to using an identity column?Thanks again.Tim |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-20 : 19:04:31
|
| Wherever the key is generated from, it's going to be slower than allowing SQL Server to manage it for you. I can't think of any drawbacks to using an identity column. You should try to avoid them though if a natural key exists.Tara |
|
|
|
timgaunt
Posting Yak Master
115 Posts |
Posted - 2005-04-21 : 02:48:09
|
| Superb thanks, so in a username type table it'll be better to use the username as that'll be unique and sace space :)Thanks again TaraTim |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-21 : 12:24:41
|
| Well usernames some people would argue could change due to marriage or whatever. You don't want to have to modify the PK data. So some people would suggest that you use an identity column here too. The key is to have a non-changing unique value for the PK. And the smaller in size the better for performance reasons. I've got one project that uses identities for everything, I mean absolutely everything. I've been trying to get them to see the way, but it's been hard. I've got another project that never uses them and possibly should on a couple of tables.Tara |
|
|
|
|
Key's Advice