On 2017/03/31 12:08 PM, 邱朗 wrote:
Say my mobile app has a customer table, a product table, and an order table to
record who buys what, the order table basically has 2 foreign keys, customer_id
& product_id.
Now I got the order information first, within in it I can't find the customer information
in my local sqlite table. As it turns out this is a new customer, whose information will
come later from another thread/queue. To make things even worse the customer id I got
from the order information is not the same one I used locally. My local customer id is
INTEGER PRIMARY KEY (I do record that "true customer id" as another column and
set index on it)
So how I do record this order information? I can come up with some clumsy solution, e.g. if I
can't find contact info, I insert a record for it first. Later after I get the real
information for this customer, I update customer & order table. But I was wondering is
there any "standard" way for the situation like this?
BTW, I did ask the same question at stackoverflow, but because I use sqlite
(while all the data come from web storing in MySQL) I was wondering if sqlite
has any specific solution for it.
Your question would arise no matter which DB you use, so it's not really
an SQLite question - but - we're a fun bunch of people, and many here
would have run into the same problem, so you might find some answers.
To start with, Your "clumsy" idea is not so clumsy, it is a practice
some people use. The flaw in that is when the customer actually existed
already, you just didn't know who it was, so now you end up with 2
records that mean the same customer with disjointed keys and Orders
connected to both.
What we usually do is put any orders that are new in a table that looks
exactly like the orders table (only without any foreign key constraints
and such) and then move them to the main order table as soon as they are
confirmed and connected to a customer. This also helps if this is, for
instance, an online interface or help-desk type system and people can
make orders which they might still cancel or amend, etc.
The biggest problem with this method is that for queries on "current
orders" you would need to join the output from the two tables - easy
enough to do, but if you already have millions of lines of code, it
could involve a lot of changes.
Another option is to create an "Unknown" customer, and link any new
orders to it. You can easily change that parent-id on the order later.
If however your problem boils down to you not being "sure" if you have
now the correct parent (at the point of insert) - we can't help you with
that. You have to either be sure, or use a temporary situation until you
are.
Whatever the solution you find, at the point stuff gets inserted to a
permanent table with foreign key constraints, all the correct
constraint-related information/links has to be known.
HTH - Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
