Hi, Thanks for answering my question. But no matter whether it is "a problem in definition" or other reasons, it already happened in my system (And that was why I asked the question in the first place). And if we just ignore my particular question, I think it is not uncommon that many to many relationship data come first and how do we deal with it.
Qiulang At 2017-04-05 17:14:02, "petern" <peter.nichvolo...@gmail.com> wrote: >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
