[RLUG] Is there a database guru out there?

[Ben Johnson]

On Sun, Aug 14, 2005 at 11:58:17PM -0700, James Washer wrote:
> My first question: If I want to insert a row in a table ONLY if that row does not already exist in the table, how is this normally done? I know I can define the table requiring entries to be unique, but I assume that means I'll generate an error condition if I try to insert a duplicate. Is that the "best way"? 


this depends highly on what the data is and how it's collected.  most of
the time, new data will simply be unique, or it's made instantly unique
by a auto_increment field.  If you can't be sure ahead of time whether
your insertion will work, you either have to run a select command to
look in advance for the record, or you have to insert and check your
error code. inserting and checking for an error would be most efficient.


> A few more details. I have a many to one relationship. For example, let's say I take an order from a customer. If the customer is new, I need to enter them into the database. Further, I need to "link" the order to the customer. How is this normally done.  BTW, I'm using perl/DBD/DBI to handle the data entry.

If you want to make sure all or nothing happens then use transactions.
In this situation, I'd say adding a new customer could be considerred an
operation that may be performed with or without a new order being
successfully linked, so there's no reason to bind the two insertions in
this way.  just create a new customer, then somehow get the new customer
id and use that as part of the order data you insert later.  If a
customer cannot exist without a linked order then do a "BEGIN"
transaction before you insert anything, then "COMMIT or "ROLLBACK"
depending on whether errors were encountered.


> Oh, btw, this needs to be fairly efficient, since I'm entering 20K records per hour. (no, this isn't really an customer order database, it's a telemetry logging application, but it was easier to explain as a customer order system)

I doubt 20K/hour (5.5/second) will be a problem.

- Ben