Thanks to everyone for their answers. - jim
On Mon, 15 Aug 2005 08:34:17 -0700 (PDT) Bill Cunningham <[EMAIL PROTECTED]> 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 <[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 _______________________________________________ RLUG mailing list [email protected] http://lists.rlug.org/mailman/listinfo/rlug
