Hang about, worrying about the relational ID's should be taken care of by
your transfer scripts.
Inserting the data in the parent table first and then sub populating any
other relient tables.
I'd always lean towards automatic numbering, it's more reliable than doing
it yourself. Also
you can easily get the value the db just created for you by selecting
@@Identity (T-sql) from a query
in the same scope. Can't remember the oracle. As a rule I always use stored
procedures to do ALL (aside from dynamic sql)
my sql. It makes maintaining an application much easier. Regardless of
the application server I use, be it
ASP, PHP, CF, a Servlet or Bean.
Max
----- Original Message -----
From: <Alex Husic <[EMAIL PROTECTED]>>
To: "SQL" <[EMAIL PROTECTED]>
Sent: Friday, January 04, 2002 11:19 AM
Subject: Re: Questions
> Point 3: create a combined primary key out of two or more columns the
> combination of which is always unique.
>
> Alex
>
> ----- Original Message -----
> From: <charles arehart <[EMAIL PROTECTED]>>
> To: "SQL" <[EMAIL PROTECTED]>
> Sent: Friday, January 04, 2002 4:44 AM
> Subject: RE: Questions
>
>
> > 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
> > >
> > >
> > >
> >
> >
>
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
