"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."This solution seems not work (especially in my case) because it is easy to have more than one unknown customer. Then I can't decide who orders what later.
The other solution (from stackoverflow) is to use "Deferred Foreign Key Constraints". That seems quite promising. Thanks, Qiulang At 2017-03-31 19:44:56, "R Smith" <rsm...@rsweb.co.za> wrote: > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
