Qiulang. I am curious about this requirement. Is there an example commerce site in the real world where having a one to one match in a master customer login table to all real customers is vital to the mission? What sort of business would have that? Even banks do not have such surety about customer entity if the entity has multiple tax numbers.
It is also normal for customer-centric systems to later allow login identities and their transaction histories to be consolidated by manual intervention given the login owner wishes it and common identity can be satisfactorily established. There are also practical and lawful reasons why some entities would need or prefer to have multiple login identities. FYI, if you are trying implement a just-in-time shipment consolidation function, couldn't an extra order process page/form be added where different login with identical name and delivery address makes subsequent order(s) during the same shipping cycle? This condition can be detected very easily by simple query over the hypothetical tables you've mentioned. Peter On Tue, Apr 4, 2017 at 9:38 PM, 邱朗 <qiulang2...@126.com> wrote: > "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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
