[thelist] SQL: insert into mutiple tables help

[Warden, Matt]

> > 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

Um, well, ok. But, assuming that you *CAN* trap database errors in the
middleware, what will happen with your method in this situation?

Request 1 will insert the data.
Request 2 will fail because that data already exists.

If I understand everything about this situation, the user will see the
result of request 2 (the request made last), which will be an error message
saying that data already exists.

Depending on the data, the user may be quite confused at how the database
can already hold his or her personal information (or IS it their
information?). Other possibilities would be that the user gives up (if
he/she has only one email address), or the user supplies different (possibly
inaccurate) data in order to get by the unique constraint.

Personally, I'd rather have a dupe record than a record from a user that
will never return or do anything with that record... or a record full of
junk data. Dupes are a little easier to find (it's just deciding which
record to keep).

I see your side. But, you're a DBA (or data modeler, or byte sculptist, or
whatever it is you like to be called  =P), and we obviously see things
different.

Also, the fact is that you don't always have the ability to trap database
errors in the middleware. In fact, the only time I get that opportunity is
when I'm writing in Java or PHP. Other languages force you to lose other
functionality if you want to trap errors in the middleware.

So, um, what was this thread about again? *shrug*




--
mattwarden
mattwarden.com