0

My application parses a web resource for data constantly. The data is then used to build a domain object. The domain object is represented in my database across several tables. The domain object is defined by the class below, Order, and its db table equivalent is named order. The OrderItem class that is part of its composition is stored in the table order_item;

class Order
{
    public string Id;
    public int Amount;
    public List<OrderItem> Items;
}

class OrderItem
{
    public string Id;
    public string Name;
    public decimal Price;
}

After parsing a web resource, I would like to insert the Order into the database if it doesn't exist. The way I would do this without the 'if it doesn't exist' constraint is to add an insert statement for the order object, and a separate insert statement for every orderitem object it contains, as part of a transaction. I am not sure how to structure it with the unique constraint, because if i do insert ignore/or on duplicate update, then the order items will be inserted regardless. What is the best way to handle these scenarios? I am using mysql and c#;

3
  • I don't understand. Each new "order" will have several "items". Why would you already have the "order"? Are you later adding more "items" to an existing "order"? Of so, phrase it something like that. Then we can discuss how to get the order_id for the items.
    – Rick James
    Commented Jul 30, 2020 at 5:21
  • As I stated, I am parsing a web resource for orders. The id's associated with these orders (Invoice #'s or whatever you would like to call them) are not generated by me, but are unique to the order. While the id of an order does not change, the details of an order might; details include address/telephone/order state/etc. After parsing the web resource, I need to either insert the order into the db, or update it if it already exists. If it doesnt exist its straightforward. Otherwise, since they are structured as separate inserts, the order insert will do nothing, but the details will complete.
    – cubesnyc
    Commented Jul 30, 2020 at 11:48
  • How do you know whether the new row is an existing order or a new order?? I don't see invoice_id in orders -- is it Id? Is that distinct from OrderItem.Id? When moving to a database, the concept of List must implemented as multiple rows in the "item" table, with a 'link' (say, invoice_id) back to the other table.
    – Rick James
    Commented Jul 30, 2020 at 15:05

1 Answer 1

0

... if it doesn't exist ...

See

INSERT IGNORE ...

And

INSERT INTO .. ON DUPLICATE KEY UPDATE ...

Both require a suitable UNIQUE key to catch its "existence".

1
  • ..."because if i do insert ignore/or on duplicate update, then the order items will be inserted regardless"...
    – cubesnyc
    Commented Jul 30, 2020 at 3:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.