| Author |
 Topic  |
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
 Posted - 03 March 2001 : 18:10:30
|
The ARCHIVE Tables have an ID Field of "ID"... it should be more descriptive like ARCH_T_ID and ARCH_R_ID
Reinsnitz (Mike)
><)))'> Need a Mod?
"Therefore go and make disciples of all nations,..." Matthew 28:19a |
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 03 March 2001 : 19:18:43
|
to be honest it could be removed, since it isn't actually required for anything
|
 |
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 03 March 2001 : 19:34:34
|
it's just good practice to have an ID field for each table.
Reinsnitz (Mike)
><)))'> Need a Mod?
"Therefore go and make disciples of all nations,..." Matthew 28:19a |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 03 March 2001 : 19:42:40
|
it can also be a performane issue.
if you create an auto in field in sql and do not create any indexes on the table, the auto id field is the default index, so if you never use the id field in where clauses then it has an adverse affect on you performance
|
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 03 March 2001 : 19:52:41
|
I don't fully understand why... but two different DBA's I've talked with on two different issues completely have strongly indicated that it's a good practice.
Reinsnitz (Mike)
><)))'> Need a Mod?
"Therefore go and make disciples of all nations,..." Matthew 28:19a |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 03 March 2001 : 20:02:55
|
I think what they were trying to say was that each record in a table should have an ID field (ie a means to distinguish each record in the table as unique) this is not the same as an 'identity' col, which is just a special kind of datatype which aut increments numbers. the archive tables already have this in the reply/topic id.
In fact as a SQL sytem admin and developer, I do not encourage the use of autoinc fields, and generally use either triggers or a function to create ID's
Edited by - huwr on 03 March 2001 20:04:01 |
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 03 March 2001 : 21:07:46
|
yesa :) There have been many times I wished we could impliment the ID field incrimentation into the forum code... but then you can't realy have as much control... and requires something like a global.asa
Reinsnitz (Mike)
><)))'> Need a Mod?
"Therefore go and make disciples of all nations,..." Matthew 28:19a |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 03 March 2001 : 21:28:59
|
quote:
In fact as a SQL sytem admin and developer, I do not encourage the use of autoinc fields, and generally use either triggers or a function to create ID's
You're more a Celko advocate than a Date advocate, I guess. I nearly always use artificial ID's as PK's, probably because of many years working with hierarchial DB's prior to getting into relational DB's.
I don't know if there is a 'right' and 'wrong' way in this regard. There have been spirited debates on the subject for quite a long time. Not quite as hot as the MS-Linux type debates though :)
======
Doug G
====== |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 03 March 2001 : 21:35:12
|
actually, I have been thinking about this, and it would be fairly simple.
the forum stores it's date as
20010110095939 if you then append to this the usersid, you get a pretty unique number, unless the same person posts again within a second, but you could minimise this risk by getting the id when they enter post.asp, so by the time they have written and posted the message, it is likely to have taken more than 1 sec.
The only difference would be for the user_id you could maybe do this by using a simple application counter which you could lock when incrementing
|
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 03 March 2001 : 21:57:31
|
but triggers/auto incriments work much better.. no questions.. no worries.
Reinsnitz (Mike)
><)))'> Need a Mod?
"Therefore go and make disciples of all nations,..." Matthew 28:19a |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 03 March 2001 : 22:18:20
|
triggers yes, auto increments quetionable, they generally cause more headaches when coding than they give benefits, in large client server databases using many to many or one to many relationships it is often desirable if not necesary to know the ID of the record you are about to insert, this is not possible using auto increment fields.
|
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 04 March 2001 : 16:58:38
|
I'm directly refering to Snitz Forums 2000 here ;)
Reinsnitz (Mike)
><)))'> Need a Mod?
"Therefore go and make disciples of all nations,..." Matthew 28:19a |
|
|
|
cevans
Junior Member
Canada
101 Posts |
Posted - 05 March 2001 : 06:02:33
|
quote:
it is often desirable if not necesary to know the ID of the record you are about to insert, this is not possible using auto increment fields.
Actually, with SQL Server, you can get the (just-inserted) value of an identity column. You need to either do it in the same connection which made the insert (a stored procedure works best). This will return the value aliased as id_value:
select @@identity as id_value
That being said, I fully agree that identity fields are more of a hastle/hindrance than a benefit!
Clark |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 05 March 2001 : 07:37:31
|
quote:
quote:
it is often desirable if not necesary to know the ID of the record you are about to insert, this is not possible using auto increment fields.
Actually, with SQL Server, you can get the (just-inserted) value of an identity column. You need to either do it in the same connection which made the insert (a stored procedure works best). This will return the value aliased as id_value:
select @@identity as id_value
Yes but only for MS SQL
quote:
That being said, I fully agree that identity fields are more of a hastle/hindrance than a benefit!
I think we should distinguish here between identity fields and Auto Inc fields, identity fields are essential for referential integrity in large data applications
|
|
|
|
Reinsnitz
Snitz Forums Admin
USA
3545 Posts |
Posted - 05 March 2001 : 08:39:22
|
and in an application like SF2K... the auto incriment field is also essential... and is not a cumbersome thing at all... it's actualy a wonderfull thing!
Reinsnitz (Mike)
><)))'> Need a Mod?
"Therefore go and make disciples of all nations,..." Matthew 28:19a |
|
|
|
HuwR
Forum Admin
United Kingdom
20584 Posts |
Posted - 05 March 2001 : 10:15:09
|
quote:
and in an application like SF2K... the auto incriment field is also essential... and is not a cumbersome thing at all... it's actualy a wonderfull thing!
Reinsnitz (Mike)
><)))'> Need a Mod?
"Therefore go and make disciples of all nations,..." Matthew 28:19a
I would certainly agree with that, any other way would involve compromises on one db or another, this way is simple and makes it reasonable easy acroos different platforms.
Edited by - huwr on 05 March 2001 10:15:36 |
|
|
|
Topic |
|
Forum Locked
Topic Locked
