Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
creating reusable primary keys
Author: Chunshen (Don) Li
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32900#165198
>I guess this is more a DB question, but I'm thinking there is some
...
>
>Nick Cabell
First of all, let me say, I agree with the most people's opinion "minor space
gain with quite a bit of effort may not be worthy". Now, on the technical
feasibility of the problem, I believe yes, here's a starter,
use UDF, then, map it into Table DDL, say, table name, tmp,
-- the UDF may look like this
create function keyVal()
returns int
AS
begin
declare @intID as int
if exists (select * from tmp)
select @intID = (select Min(keyCol) + 1 from tmp)
else
select @intID = 1
RETURN @intID
end
On a similar note,
>[Tom Kitta]
>My philosophy is to make things as simple as possible (but not simpler)
I fully
agree.
Author: Ewok
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32900#165137
I dont believe he meant flag it and leave it... what would be the point of
that... he meant flag it then the next time you add a record just UPDATE a field
flagged as deleted...
(which is probably the only way to do what he wants easily... prepopulate 255
records and flag them all deleted to start out)
I usually set it to a normal int and PK then use MAX(pk) to add 1 to and get the
new number if I want reusable numbers
your not going to be able to keep it in the ranges of 1 - 255. eventually its
going to need higher numbers unless you waste a lot of time and server processing
to figure out what numbers are available in that range, 1 or 2 k isnt going to
kill you when you said yourself.... your never going to have more than 255
records in it anyway
That would defeat the original purpose of saving space in the DB. Besides,
integers aren't that big and space is cheap these days. Why go through the
trouble of doing all that to save less than 1kb? Anyone remembers win2k? How
did it happen - some space saving issue, wasn't it?
[Tom Kitta]
My philosophy is to make things as simple as possible (but not simpler) if
at the same time I can get some free goodies like performance or extra free
space than I go for it. But KISS rules most of the time, if not all of the
time. Just my .02c.
TK
http://www.tomkitta.com
Yes, don't delete the records -- just flag them as deleted (have a
column in the db to indicate the the record is deleted)
Dick
On May 31, 2004, at 4:53 PM, Nick Cabell wrote:
> I guess this is more a DB question, but I'm thinking there is some
> wisdom on how to create PKs in CF that I don't have.
>
> I am being space conscious and have declared a PK for a table
> with a data type of TINYINT because I will never have more than
> 255 records. My records don't have suitable unique values so I wanted
> to
> use the IDENTITY attribute in SQL Svr to automatically create the
> primary keys. Trouble is that once I delete one of the records,
> that PK value will never be used again.
>
> Is there a clever way in CF to create the PK yourself and be
> able to reuse the value when it is deleted.
>
> Nick Cabell
> 451 Learning Systems
> (650) 823-1858
>
Author: Rick Root
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32900#165104
The difference between a tinyint (1 byte?) and a char(35) (35 bytes) on
a table that will never contain more than 255 records is pretty minimal.
8k, maybe. Is it really that important to save 8k of space?
Just use a char(35) and use the CF CreateUUID function.
- Rick
Author: Tom Kitta
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32900#165091
That would defeat the original purpose of saving space in the DB. Besides,
integers aren't that big and space is cheap these days. Why go through the
trouble of doing all that to save less than 1kb? Anyone remembers win2k? How
did it happen - some space saving issue, wasn't it?
[Tom Kitta]
My philosophy is to make things as simple as possible (but not simpler) if
at the same time I can get some free goodies like performance or extra free
space than I go for it. But KISS rules most of the time, if not all of the
time. Just my .02c.
TK
http://www.tomkitta.com
Yes, don't delete the records -- just flag them as deleted (have a
column in the db to indicate the the record is deleted)
Dick
On May 31, 2004, at 4:53 PM, Nick Cabell wrote:
> I guess this is more a DB question, but I'm thinking there is some
> wisdom on how to create PKs in CF that I don't have.
>
> I am being space conscious and have declared a PK for a table
> with a data type of TINYINT because I will never have more than
> 255 records. My records don't have suitable unique values so I wanted
> to
> use the IDENTITY attribute in SQL Svr to automatically create the
> primary keys. Trouble is that once I delete one of the records,
> that PK value will never be used again.
>
> Is there a clever way in CF to create the PK yourself and be
> able to reuse the value when it is deleted.
>
> Nick Cabell
> 451 Learning Systems
> (650) 823-1858
>
Author: Dave Watts
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32900#165089
----- Excess quoted text cut - see Original Post for more -----
I would strongly recommend that you simply use a regular INT column that
will let you use IDENTITY values normally. Your desire to be
"space-conscious" will cost you more than it's worth. In addition, I would
strongly recommend that you not attempt to reuse surrogate primary key
values. This may add significant complexity to the database, and won't
provide any benefit.
Can you explain what exactly you're concerned about, with regard to space?
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
phone: 202-797-5496
fax: 202-797-5444
Author: Dick Applebaum
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32900#165088
Yes, don't delete the records -- just flag them as deleted (have a
column in the db to indicate the the record is deleted)
Dick
On May 31, 2004, at 4:53 PM, Nick Cabell wrote:
----- Excess quoted text cut - see Original Post for more -----
Author: Nick Cabell
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:32900#165087
I guess this is more a DB question, but I'm thinking there is some
wisdom on how to create PKs in CF that I don't have.
I am being space conscious and have declared a PK for a table
with a data type of TINYINT because I will never have more than
255 records. My records don't have suitable unique values so I wanted to
use the IDENTITY attribute in SQL Svr to automatically create the
primary keys. Trouble is that once I delete one of the records,
that PK value will never be used again.
Is there a clever way in CF to create the PK yourself and be
able to reuse the value when it is deleted.
Nick Cabell
451 Learning Systems
(650) 823-1858
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
