3 Replies Latest reply on Jun 23, 2010 6:55 PM by comment_1

    Overall DB Design Questions



      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. 



        • 1. Re: Overall DB Design Questions

          A student marries and changes her name. Another student complains his name is "Smythe" not "Smith". In both cases, when you correct the name in the Students table, you break the relationship to the records in the other table.



          JosephB wrote:

          Is there something I should be concerned about as far as creating a gigantic table like that?

          Hard to answer without knowing what "Records" are in real life.


          • 2. Re: Overall DB Design Questions

            OK, thanks.  I am convinced, but I want to make sure I set this up correctly. 


            Students Table:StudentID = auto-generated serial #


            Records Table: StudentID = text or number



            • 3. Re: Overall DB Design Questions

              Yes. It's best to use the same data type for both fields.


              BTW, you do not have to remember the StudentID. You can use the name for finding records, etc. Once you set it up, you can forget it exists.