Again, ditto's to Perrin.

There is a number of good database tutorials on this site.

But to add: If you use normalised tables the way Perrin has suggested, where the student table has an ID which is used in the table of exams take to link back to the student, and also in the exam questions answered then you can flexibly add and/or delete form teh questions answered and other tables without having to backtrack through the other tables.

Putting in a 'multi-attribute' record (such as questions answered in your description), might have been okay in a system like Pick, which supported just this trick, but not in an RDBMS.

So you would need a table for STUDENT, a table for EXAM, a table for QUESTION and an ANSWER table which ties together student and question.

In this way you can then get all of the data you want with a series of fairly simple SQL constructs using JOINs to get the final result set. Any of the good books on MySQL (MySQL Reference Manual, from O'Reilly, MySQL by DuBois from SAMS are my two favourties and the MySQL Cookbook also from O'Reilly is very useful too) will give you an entre into SQL queries.

jdtoronto

In reply to Re: SQL database architecture by jdtoronto
in thread SQL database architecture by punchcard_don

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.