[RLUG] Is there a database guru out there?

[James Washer]

Thanks to everyone for their answers.

 - jim

On Mon, 15 Aug 2005 08:34:17 -0700 (PDT)
Bill Cunningham <axoibc at yahoo.com> wrote:

> James,
> 
> Generally you provide a primary key which is unique per row
> in the table. This can be one or more columns that when
> combined form a unique "key" for accessing the data. Then
> whenever you try to reinsert a new key you get an error
> message statings its a duplicate. For the lazy this is
> usually an ID field but that causes problems if you have to
> replicate the table somewhere.
> 
> You next question about customer/order information is
> usually done with referencial integrity checking.
> Unfortunatley MySql doesn't have this capability yet ( at
> least that I've seen ). What you would do is make the
> primary key on the client table referenced from the order
> table:
> 
>  client table         order table
>   PK            <->      FK
> 
> which becomes the foreign key in the order table. This
> prevents two things. First you can't insert an order
> without a client and secondly you can't delete a client
> without removing all the orders (usually). This prevents
> the big two mistakes that take the most time to undo.
> 
> BTW Postgresql does have these features.
> 
> 20K records per hour is nominal, as that is only 5
> transactions per second. I would expect most systems to
> start getting taxed around 18 - 22 per second. Anything
> more than that and you need to start spreading it to more
> than one box. Be sure that your box has plenty of log space
> though. Also try not to put any indexes except the primary
> keys on the two tables, as this will impact insert
> performance.
> 
> 
> - Bill
> 
> 
> --- James Washer <washer at trlp.com> wrote:
> 
> > I'm just starting to play with mySQL and have a few
> > questions.
> > 
> > 
> > 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"? 
> > 
> > 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.
> > 
> > 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)
> > 
> > thanks
> > 
> >  - jim
> > 
> > _______________________________________________
> > RLUG mailing list
> > RLUG at rlug.org
> > http://lists.rlug.org/mailman/listinfo/rlug
> > 
> 
> 
> Bill Cunningham 
> Cell: (775) 813-6892
> http://www.cunndev.net
> 
> _______________________________________________
> RLUG mailing list
> RLUG at rlug.org
> http://lists.rlug.org/mailman/listinfo/rlug