Hello EveyOne, > > For example, if the Phones column is a collection, one can find > all > > rows that contain the phone number 206-481-4442 by specifying the > > following SELECT statement: > > > > SELECT LastName > > FROM Contacts > > WHERE Phones(ANY) = "2064814442" > > Why not encode Phones as ":phonenumber:" > that way you can already do: > SELECT LastName FROM Contacts WHERE Phones LIKE '%:phonenumber:%' >
I think this is not practical > (btw, your quoting is wrong) > > > If one always store fax numbers in the third element of the > Phones > > collection, one could find all rows that contain a particular fax > > number > > like this: > > > > SELECT LastName > > FROM Contacts > > WHERE Phones(3) = "2064814442" > > SELECT LastName FROM Contacts WHERE Phones LIKE > ':%::%::phonenumber:%'; This is also not proctical > > > For example, lets return to the contact database, which contains > a > > Phones column whose > > data type is PhoneStructure, so the Phones column contains the > > columns PhoneType and PhoneNum. The following query could returns > > just the phone number 206-402-9400 and not the phone type for > that > > number: > > > > SELECT Phones(*)!PhoneNum > > FROM Contacts > > WHERE LastName = "Calder" > > This didn't require any special syntax. Because Phones can contain more than one Items we require special syntax > > > The following query would return the phone numbers for every > contact > > who has a fax number: > > > > SELECT Phones(*)!PhoneNum > > FROM Contacts > > WHERE Phones(ANY).PhoneType = "fax" > > Instead of encoding phone numbers as :phonenumber:, you could also > use > :type=phonenumber: Ok this can be considered but yet it looks quite confusing > 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) > 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...! > > > > 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....?! > >SELECT LastName FROM Contacts WHERE Phones LIKE > ':%::%::phonenumber:%'; I agree with u here > 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? > > 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 Thanks Yogi Yang __________________________________ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
