Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?
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" 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, 邱朗 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" 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 y
Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?
On 3/31/17 6:08 AM, 邱朗 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. Thanks, Qiulang This sounds like a problem in definition. Normally one would not 'book' the order into the main database until it is complete and actually placed, and at that point you should have all the data (you don't want order delivery to start shipping the item(s) until the order is confirmed and payed for) . While the order is being built, you normally keep track of the data in a separate place, a 'shopping cart', and for those things, you will be keying to shopping cart id (a session or cookie id). -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?
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, 邱朗 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" 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.sq
[sqlite] If I got many to many relationship data first, how do I insert them to my table?
"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" 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
Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?
对于这样的问题SQLite也没有特别的解决方案。你说的方法几乎就是“标准”的做法,先把customer_id 设置为null, 然后插入该订单,( 这样就不会引起外部键检查失败), 当获取该订单准确的customer_id后,再把它更新。 App的逻辑应考虑到这一点,所有customer_id 为null的订单都是不完整的,相当于草稿。 Zhai Sent from Mail<https://go.microsoft.com/fwlink/?LinkId=550986> for Windows 10 From: 邱朗<mailto:qiulang2...@126.com> Sent: 2017年3月31日 18:09 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> Subject: [sqlite] If I got many to many relationship data first, how do I insert them to my table? 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. Thanks, Qiulang ___ 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
Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?
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] If I got many to many relationship data first, how do I insert them to my table?
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. Thanks, Qiulang ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users