[RLUG] Is there a database guru out there?

[Bill Cunningham]

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