If you are trying to be sure that your database schema is well designed, I believe there is no reasonable way to test. Instead, schema design is best done by careful thought followed by a second or third set of eyeballs. There are many resources available that will help you understand database schemas.
Thanks griswolf. I'm sure you're right, but unfortunately I don't have the extra sets of eyes, so I have to do it the hard way. I've found SQLite Administrator and I'm going to use it to write the queries and run them. I think it's what I need to test with.
Ah. I see you are planning to test that your queries work as expected against your database schema and data. For that, I strongly recommend some kind of unit test strategy. Pick a unit test tool that supports the language you will use in the application itself, and write a set of tests that:
at setup: Create and populate the tables with suitable test data (be aware of NULL and other extreme values as well as 'regular' data).
in the test, assure that the query gives you the expected output. Be sure to use ORDER BY so as to avoid inconsistent order for the same results
at teardown: Drop all the tables
If you must use the Administrator tool, then be sure to write a script for what you intend to do so it can be duplicated later, extended to handle regressions, etc. (In other words: Make some unit tests).
Thanks for the reply and the info, griswolf. I hate to seem overly dumb, but can you tell me what a "unit test" and "unit test tool" are? the way you are speaking of them makes me think that they are what I need, but I've never heard of them.
Look through a few of the articles found using Google to search unit testing . Then, search a little more specifically: unit testing [I]your-language[/I] to find out about unit test packages for your-language. I know for sure that there are unit test packages for Java and Python (having used them) and am aware of unit test packages for C++, C#, Visual Basic, lisp. "Google unit testing sql code" might be interesting to you...