Hey all -
tl;dr? Check the bold type.
In the beginning of November I made a post about queries, and the discussion led back to the design of my database. I've done some thinking and tinkering, and a little more research. I still have a lot of uncertainty about the work I've already done, and would like more guidance. I also have a thread from September about a solved issue, which includes the file of an earlier but similar database in post #8, in case anyone wanted to see a sample of what I started.
Firstly, aytee111 wrote in a post that "People use autonumbers as primary keys so as to disguise the underlying bad table design!" Are autonumbers generally frowned upon for use as PKs? I feel there are some instances where you would have to use one, such as my "TeacherInfo" table (see below). I've also learned about composite keys, and am curious if that would be useful to my design.
I have brought myself back to the planning stage, and my uncertainty comes from the fact that I feel my current work has everything I need, but it's organization/normalization is slightly faulty. When I write down what I want from my database, I wind up with a plan that looks similar to what I already have. Here is the context of the database, followed by a brain dump: I run an education program, and I offer several environmental education programs to local teachers. The database will be used by me (and maybe a coworker or two) to enter, store and report teacher information. It will also enter, store and report any programs in which the teacher has participated. The data will be entered via a form which will also have an option to generate numerous reports on the data.
I want my database to show:
- Information on teachers.
- Which programs teachers have attended.
- When teachers participated in programs.
- How many students participated in programs.
With the data, I should also be able to run queries/generate reports that focus in on specific types of programs (e.g. how many field trips vs. in-school programs) and the attendance of those programs (how many students attended field trips vs. in-school programs).
My design so far has included three tables (some names have changed as I've worked on this project):
- tblTeacherInfo (all pedigree info like name, school, grade level, contact info)
- tblTypesofPrograms (lists the programs that we offer)
- tblProgramsGiven (stores which program, which teacher(s), what date, how many students)
Based on all this, how would I create the relationships between these tables? Do I need more tables? Should I be using any autonumbers for PKs, or can a text field be a primary key (all program names are unique, so should that be the primary key)? Should I make a composite key from 3 rows, or is that indicative of bad database design? Should a foreign key be a part of a composite key? If I need to supply more info I gladly can; I left it somewhat vague because I would like your thinking/advice to be as fresh as possible (i.e. not tainted by my lack of database design experience).
Thanks as always,
Joe