I'm not sure I agree with the "always use a manual numbering scheme". While
the case of portability is a worthy one, it's not a critical design factor
for all db applications.
Now, you may ask why not have the benefit anyway if it's as easy as you
suggest in step 3 ("Create a new table, and then assign a unique value to
it. You can do this in CF. Just loop through the table and add a new number
each time.") Do you really mean to suggest one should "loop through the
table" to get a new id?
Also, in a high-volume application, what are you doing to ensure that you
don't inadvertently get the same new value from two threads running this
code at the same time? If you're relying on the database to handle the
concurrency issue, then you'd better indicate which isolation level you
expect the dbms to support, and then use a CFTRANSACTION to indicate that
around the process of finding the current high value and assigning the next
high value.
I'd appreciate hearing your thoughts on these things. There are always
tradeoffs in design decisions. I realize that not all apps will be
high-volume enough to worry about this issue, but then again it's no less
worthy a design factor than portability. It seems both subjects should be
considered in debating CTLoo's question.
/charlie
-----Original Message-----
From: Bruce Sorge [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 03, 2002 10:25 PM
To: SQL
Subject: Re: Questions
----- Original Message -----
From: "CT, Loo" <[EMAIL PROTECTED]>
To: "SQL" <[EMAIL PROTECTED]>
Sent: Thursday, January 03, 2002 9:18 PM
Subject: Questions
> Dear SQL gurus,
>
> I have some questions on database design and hope all experts out there
> can give me some ideas/advice.
> Please advise/comment on the followings:-
>
> 1. Should one use the identity field as primary key in designing a
> database?
No. You should always use a manual numbering scheme, basically because, if
ever you have to move this DB to another DB, even of the same type, and it
is a truely relational DB, and you have ever deleted records (which I hear
happens on occassion), you will have a lot of work ahead of you ensuring
that all of the related tables are numbered correctly.
> If yes, isn't it has a limitation when the number reaches its
> limits?
> 2. What would happens to the database when the identity field's value
> reaches its limits?
Another reason to use a manual numbering scheme.
> 3. Any advice on selecting a primary key for a table where none of the
> columns has unique value?
Create a new table, and then assign a unique value to it. You can do this in
CF. Just loop through the table and add a new number each time.
>
>
>
> Thanks in advance.
>
> Kind regards,
> CTLoo
>
>
>
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
