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 

Re: [sqlite] If I got many to many relationship data first, how do I insert them to my table?

[Richard Damon]

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?

[petern]

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 

[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?

[Quan Yong Zhai]

对于这样的问题SQLite也没有特别的解决方案。你说的方法几乎就是“标准”的做法，先把customer_id 设置为null, 然后插入该订单，（ 
这样就不会引起外部键检查失败）， 当获取该订单准确的customer_id后，再把它更新。
App的逻辑应考虑到这一点，所有customer_id 为null的订单都是不完整的，相当于草稿。

Zhai

Sent from Mail for Windows 10

From: 邱朗
Sent: 2017年3月31日 18:09
To: SQLite mailing list
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?

[R Smith]

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