On Mon, Sep 28, 2009 at 10:28 AM, John <jhy...@earthlink.net> wrote: > CityDev wrote: >> You seem to be asking about four separate issues - normalisation, table >> creation, table loading, and SQLite syntax. The thing is I've just looked at >> your book's index and I can't imagine a better source of answers to your >> questions. >> >> Maybe you would like to post some specific cases here? > > Yeah, I guess it is a little vague, I'm just getting oriented in > database programming. I've found a lot about foreign keys and > sqlite3 now, and lots of people show examples of using triggers > to enable foreign key checking in sqlite. But I think to start, > I don't need that checking if I code some simple checks on fields > first. > > Anyway, what I was looking for was an explicit example of creating > two tables with the required primary key designations, and using > those keys to populate them so they are normalized (to some level > at least). For example, call one table person and the other foods. > Then John and Jane are two persons to put in the person table. Then > the foods table is apple, orange, banana, and peach. John eats > apple, orange, and banana. Jane eats apple, banana, and peach. > I was hoping to see a simple example of table creation (mostly > how to use primary keys between the two tables; foreign key > apparently) table population, and maybe some data retrieval. > I think I'm beginning to understand how to do this, but I learn > best from examples. >
Search for normalization and relational databases using your favorite search engine. You will get a lot more information than you can wave a stick at, and in a format more useful than a mailing list could provide. > I'm thinking that first, I'll populate the people table, then the > food table. Then, a column (can't be a primary key column) will > be set to the primary key number of a food in foods. Do this for > John and Jane and each food they like. Then, how to handle a > new food, grapes. Something like this: > 1. see if grapes exists in table > 2. if yes, look up the key number and insert that in the column > in the person table (if person not in table, insert person first) > 3. if no, insert grapes into the food table, place the newly created > primary key number in the appropriate person column > 4. do that for each new food > 5. write a join sql query to return which foods a person likes > > So I'm looking for a simple coded example of something like the > above, including checking if person and food entries are in the > table or have to be created on the fly. > > If the above logic looks reasonable, I may be able to mull my > way through to a solution. I think the approach won't need > enforced foreign primary keys and be robust? > > Hmmm. It just dawned on me that the trigger enhanced code may > be what "protects" you from "accidentally" changing or deleting > a key number and breaking data that depends on it? I don't > think my simple code above needs that as long as I never > delete a key or food. If I do want to delete a food, say, I'd > just have to write sql to see that no person likes that food > before deleting. I guess maybe triggers make this easier to > program somehow? > > As for the book's index, here is what I've penciled in to my > book, and I've just begun: delete 128, dump 42, import 42, > insert 123, like 92, pragma 381, unique 129, update 127. > Agreed, many of these do appear under "Structured Query Language > (SQL) Syntax", but that isn't the first place I looked each time, > and I only discovered them after a large amount of contents > checking and thumbing through the book for the topics. So they > now are in both places in my book. :-) > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, WI, United States _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
