Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>If the number of "items" (lectures, parts, widgets, colors) for an entry (each entry being a "row" in a database table) is known and fixed, there is no reason not to have one column for each of those "items". It can become very difficult to work with as each row can become quite large -- 48 lectures in your example -- but all of the "items" could be readily located by querying for the row (a student, in this case) that you wanted.</p> <p>Typically you would have a different database layout in which the main entity you wanted was the "student" and for each "student" entity there would be a number of additional entities associated with that "student". These would be the "lectures" and each "lecture" entity would be associated with a "student" and a date. In that situation, you would query the database for a "student" then use a relational database operation called a "join" to locate the student's attendance from the "lectures" table by joining the "student" identifier associated with the "lecture" table record with the name of the "student".</p> <p>The advantage of this method is that you could create additional entities -- homework assignments turned in, test or examination grades, etc. -- that could then also be located and "joined" to the student in the same fashion. As you add these different entities, instead of having to modify the original table (student name plus 48 lectures) you create new tables ("homework", "tests", "exams") and these tables then contain the student name as one field, and the date and grade as the other fields.</p> <p>A disadvantage of this method is unneeded complexity. Do you really need an additional table just to hold what cannot change -- the number of weeks in the course is fixed by the calendar and the course schedule? You have to make that decision -- if the "student attendance" database must be applied to other course, with other schedules, you have no choice but to add that complexity. If this is simply a one-off task for a single course, the complexity buys you nothing down the road.</p> <p>As Kyser said, study up on Relational Database theory.</p>
    singulars
    1. This table or related slice is empty.
    1. POIs it better to make more columns or rows in a database
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJulie in Austin
    1. USJulie in Austin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    1. COI disagree with your very first statement. Have you ever had to maintain databases that were built like that? It's nothing but a nightmare. Just because something looks "set" today doesn't mean that it isn't going to change tomorrow. Applying the spirit of the open/close principle you should really apply a reasonable amount of normalization, and when you have more than one of something this usually is an indication that normalization may be desirable.
      singulars
      1. PO
      1. USLucero
    2. COLucero: Yes, I've designed, developed and maintained databases which looked precisely that way. Don't get so carried away with normalization that you turn element in a "set" into a separate row in a table. The OP's question is fairly simple. They have a row in which each row has a number of values which is fixed. Having a "student" table, and a "lecture" table where the primary key was the student name, would mean that to find out how many lectures (forget =which=) a student attended, you'd have to JOIN the student and lecture tables. Now you have to deal with "dates".
      singulars
      1. PO
      1. USJulie in Austin
    3. COAnd what's the harm of a JOIN? Having 48 lectures as columns makes queries very cumbersome. How many did a student attend? Oh, and if you want to add more information (such as a reason why a student didn't attend, or if he/she was late to the lecture, or whatever) you're in a dead end with your database layout. Normalizing such a table at that stage is difficult and expensive if there is already code using that. I'm not carried away with normalization - but it really should be applied in exactly such cases (you can still create a denormalized view if that is necessary for a specific use).
      singulars
      1. PO
      1. USLucero
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload