Re: [sqlite] executing queries on normalized database

2013-11-16 Thread James K. Lowden
On Sun, 10 Nov 2013 14:54:17 +0400 dd wrote: > After applying normalization, there are twelve tables with foreign > key support. Well done. > For insert/delete operations, it has to execute twelve queries > instead of two. Is it recommended way? Yes. In a user-defined transaction. Each

Re: [sqlite] executing queries on normalized database

2013-11-16 Thread James K. Lowden
On Mon, 11 Nov 2013 18:35:31 +0400 dd wrote: > Can I conclude this way: Foreign keys works pretty well when > application deals with parent keys only. But, application may need to > execute more queries when dealing with child key/tables. Constraints express rules that the DBMS enforces for you.

Re: [sqlite] executing queries on normalized database

2013-11-11 Thread Stephen Chrzanowski
I agree with Simon, but I also twist things up a bit. Databases are just data storage containers, at the most low level, raw definition. A text file could be a "database" for all intents and purposes. There should be things in there to protect the data within, but there also needs to be some kin

Re: [sqlite] executing queries on normalized database

2013-11-11 Thread Simon Slavin
On 11 Nov 2013, at 2:35pm, dd wrote: > Got it. > > Can I conclude this way: Foreign keys works pretty well when > application deals with parent keys only. But, application may need to > execute more queries when dealing with child key/tables. My feeling is that a lot of this logic needs to be

Re: [sqlite] executing queries on normalized database

2013-11-11 Thread dd
Got it. Can I conclude this way: Foreign keys works pretty well when application deals with parent keys only. But, application may need to execute more queries when dealing with child key/tables. Is it? Thanks, dd On Mon, Nov 11, 2013 at 5:35 PM, Simon Slavin wrote: > > On 11 Nov 2013, at 12:3

Re: [sqlite] executing queries on normalized database

2013-11-11 Thread Simon Slavin
On 11 Nov 2013, at 12:38pm, dd wrote: > Scenario: > > sqlite> pragma foreign_keys = on; > sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY > AUTOINCREMENT, artistname TEXT); > sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY > AUTOINCREMENT, trackname TEXT, trackartist I

Re: [sqlite] executing queries on normalized database

2013-11-11 Thread dd
I understand parent table to child table modifications with ON UPDATE/ON DELETE CASCADS. It's very good. Now, I stuck with child to parent tables query optimization after normalization. Scenario: sqlite> pragma foreign_keys = on; sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY AUTOINC

Re: [sqlite] executing queries on normalized database

2013-11-11 Thread Michael Schlenker
Am 11.11.2013 12:57, schrieb Simon Slavin: > > On 11 Nov 2013, at 11:08am, dd wrote: > >> How do I insert trackname as "That's Amore" and artistname as >> "Dean Martin" with single query in artist and track tables? > > Sorry, I don't know a way to do that. I hope someone else has a way > but

Re: [sqlite] executing queries on normalized database

2013-11-11 Thread Simon Slavin
On 11 Nov 2013, at 11:08am, dd wrote: > How do I insert trackname as "That's Amore" and artistname as "Dean > Martin" with single query in artist and track tables? Sorry, I don't know a way to do that. I hope someone else has a way but it's normal to have your software figure out that you ne

Re: [sqlite] executing queries on normalized database

2013-11-11 Thread dd
Hi, Thanks Simon. sqlite> pragma foreign_keys = on; sqlite> CREATE TABLE artist(artistidINTEGER PRIMARY KEY AUTOINCREMENT, artistname TEXT); sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY AUTOINCREMENT, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE C

Re: [sqlite] executing queries on normalized database

2013-11-10 Thread Simon Slavin
On 10 Nov 2013, at 10:54am, dd wrote: > I have two tables in my database. > > After applying normalization, there are twelve tables with foreign > key support. > > For insert/delete operations, it has to execute twelve queries > instead of two. Is it recommended way? You should not have to

[sqlite] executing queries on normalized database

2013-11-10 Thread dd
Hi, I have two tables in my database. After applying normalization, there are twelve tables with foreign key support. For insert/delete operations, it has to execute twelve queries instead of two. Is it recommended way? In delete case, do always need to check in parent table whether chi