Overall DB Design Questions
I am creating a DB to track a lot of different information about students.
I am pretty much settled on doing just two tables: Students, and Records. Records would be a giant table with about 100 fields (many different types of "records" could occur). Only a few fields would be used in any one record (except Student_Name and Record_Type and Record_Description). Then a few of the remaining 100 fields would be used, and the rest left blank...but it would work. So my first question: Is there something I should be concerned about as far as creating a gigantic table like that? I would only ever look at particular groups of fields at the same time.
Next, setting up the relationship between Students:Student and Records:Student is driving me nuts. I keep reading that I should use an auto-generated number as my primary key. But it would seem easier to me to just set up a field Students:FullName as the primary key, and set the Related Student field to auto-fill in the Records layouts to avoid misspelling a name. Otherwise I am going to have to remember a students ID# when I am rushing to type in a record?
If two kids have the same name, I guess I would add a middle initial to one of them. But I keep reading warnings against using anything but auto-generated ID#s as primary keys. This DB is going to be used mostly by just me, (and for printing up reports on students for others to see...) so my second question is: can I just use students full name as a primary key or is there really something I don't know that is going to bite me back someday. I am not building a DB for a business here, so no $ is on the line.
Thanks in advance.