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.
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
