[thelist] SQL: insert into mutiple tables help

[rudy]

golly, what a nice thread this turned out to be

well, okay, nice for me and maybe a half dozen others, but i live and
breathe this stuff

the designers on the list probably don't really care about backend
intricacies, but hey, the subject line has SQL in it, they can easily skip
these posts


>Allow me to take a deep breath before defying the master...

matt, what are you talking about?  =o)

you're adding depth here, just rounding out my comments and by introducing
a few new slants, you are improving everyone's understanding...


> ... you can just define it as an incrementing autonumber and use:
>
>  select max(id) from thetable
>     where email = 'joe at public.com';
>
> Then, you don't have to be checking to see if joe at public.com
> already exists.

i know what you mean, but this is the cart before the horse, so to speak

you have to decide whether email id should be unique *before* you ever get
around to writing the queries

if it is not unique, the query above is necessary

if it is unique, the database won't let you insert more than one

(the above query will work anyway, but that's beside the point!)

now, you might want to check if a key exists before trying to insert it,
but i'll answer that in my reply to jeff

> If the user submits the form twice in a row or something
> else that causes concurrent requests with the same
> email address, who cares?

it all depends on your business model

there are pros and cons to both cases (making email unique or not), and you
have to choose -- if it's not obvious, let your interface designer or
information architect tell you which is the more suitable for your business

if email is not unique, then the above is certainly the right way to
approach getting back the last incremental id

> The only bad thing is that you have an dupe record that
> won't be used (assuming you don't have to search on
> email after you have the id).

big assumption!!

<tip>
all pk's (real ones, not surrogate ones) in a database will end up having a
search function
</tip>

and i'd rather avoid the dupes


to be continued in reply to jeff...


rudy