Previously you learned a lot about creating tables yourself or with the Table Wizard, but there is still more to learn!
You want to design your tables so that:
How does one do that??
If you read articles or books about databases, you will run into the terms normalization and normal form. There are several levels of normalization and many different ways to describe each level. These 'rules' are guides for making a database that works well and can be modified and enlarged without breaking. It's another way of looking at what makes for good table design.
From these errors, you can see that it is important to keep together only the fields that truly belong together.
The table has a primary key; every other field depends on the whole key and nothing but the key.
Many databases do not completely follow the normalization rules. Sometimes a database will actually work faster without being completely normalized. It is certainly a lot of trouble when you have a small database. But will your database stay small?
Once you have a lot of records, it can be tricky to normalize the tables. It's best to analyze your plans for the tables in your database carefully and normalize the table designs before adding a lot of records!
A table that has been normalized often has a lot of fields that are foreign keys. What you see in Datasheet View is a bunch of ID numbers. Not very user friendly! But datasheets are not supposed to be user friendly. That's for forms and reports. Avoid the temptation to put your data into tables in a way that is easy for you to read right from the table. That's probably not normalized and will cause trouble later.
The illustration shows the Datasheet View of the Scores table from the Language Arts Classes database in the resources files.
The table records student scores on the various assignments and tests. The primary key is ScoreID. There are three foreign keys: C/SID, A/TID and StudentID. It would be hard to use this datasheet to find out what Molly McCoy scored on the Unit 24 Test in Spelling. (It's ScoreID 278. She scored a 95!) I filtered a form to figure that out.
Disclaimer: The Language Arts Classes example database is based on an actual database that I created while teaching 7th grade Language Arts for 65 days. Scores are real, but student names and info are not.
It is not a perfect database. I did not fully normalize the tables, including the Scores table. This created some issues later. I had to create some queries and some manual methods to work around those issues. Awkward for sure. But, the tables and relationship structure worked well enough to suit me at the time.
Perfection is not always worth the time and the aggravation to fix when you realize a bit late what you should have done! If I had taught those same classes the next semester, I would have revised the table structure.