On Sat, 2003-12-27 at 07:16, Yogesh Vachhani wrote: > > Why exactly do you think the JOIN operator is the wrong tool for > > this? > > I feel JOIN operations are taxing on the Process as well as on RAM > and HDD (in form of temporary files)
All right then. SQLite doesn't produce temporary files for JOIN, and your query _can_ be expressed relationally as a JOIN, so it should never be any faster than a JOIN operation- except due to parsing. Can you verify that parsing is taking "too long" here? > > If you think this is easier to read, then consider creating VIEWs > > to > > store intermediate queries. > > I have never considered this as I am not a power user of SQLite. > Sorry...! VIEWs are quite normal in other SQL providers. That said, you may want to become more familiar with SQL before you start suggesting extensions to it- at the very least, be aware that syntactical additions may very well be fine, but you will face criticism if your reasoning is flawed. > > > So one can see that one would not lose anything by using SQLite's > > > extended storage model (if implemented). Even though the > > LineItems > > > information is embedded into the Invoices table, one can still > > write > > > a simple query to access it as if it were in an independent > > table. > > > > Backwards compatibility gets lost. > > API changes so programs need to be altered. > I did say at the start that we should extend the existing > functionality so that who are comfortable with the existing on will > keep on using this one and other can try out the other one! > > This not lead to lost in BACKWARD compatibility....?! If you have a table which has a value that can return multiple values you MUST alter the API of the callback function or of the fetch function. Otherwise how is older code supposed to deal with a table having multiple values in an attribute? > > That's an idea.... > > anyway, it can already be done with SQLite by supplying your own > > function. Additionally, you could add lists like this yourself > > (pick a terminator, and make a function that indexes) > > By the way does anyone know whether SQLite supports User Defined > Functions? If such a thing is possible then all such functionality > can be implementd out side SQLite in a separate DLL. What do u all > think? Yes, SQLite supports "user-defined" functions. The Wiki has a great deal of documentation about this... > > Generally speaking, I think that trying to store an unknown number > > of distinct values in one record column is a bad idea, and it > > violates level 1 normalization. You really should use a separate > > table for the phone numbers and/or addresses, one record per > > instance. > > It does not violate some of the normalization rules but then how many > follow this pracitcally. In fact I have noticed that in practicaly > implementations many times a developer has to avoid normalization > rules to implement some pecular requirements of their customers I do not think you know what you are talking about. "level 1 normalization" comes from graph theory and describes a manner in which every graph can be translated into another graph as utilizing no nested functions - which are called "level 1 normal forms". I don't think the responder quite spoke correctly either. The structure you desire _can_ be folded down (normalized) into the view normal people have of SQL: You aren't suggesting that anything be made available that isn't presently available- but you want to write these systems without learning SQL, or because you believe giving syntactical hints will make queries run faster. I do think that this reasoning is erroneous. Profile, don't speculate, and get the very basics of relational calculus down before you decide SQL is too primitive to support the data structures you need (hint: there _are_ many structures that map very poorly to SQL. the one you selected however, maps very _nicely_ to SQL). --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
