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 <[EMAIL PROTECTED]> 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 > [email protected] > http://lists.rlug.org/mailman/listinfo/rlug > Bill Cunningham Cell: (775) 813-6892 http://www.cunndev.net _______________________________________________ RLUG mailing list [email protected] http://lists.rlug.org/mailman/listinfo/rlug
