0

Is it better to make more columns or rows in a database . I am entering student attendance information in the table of attendance.

condition 1: Should i include 1 week column and 3 lecture column but this is increasing the number of rows for each student because there are 16 weeks and every week has 3 lectures.??

condition 2: Or should i add 48 lecture column which will store the attendance of each student separately.???

i saw many answers but i can not get it can some one help me?? thank you in advance

Diego
  • 34,802
  • 21
  • 91
  • 134
alternatefaraz
  • 374
  • 1
  • 5
  • 18
  • 1
    You should read up on relational database theory. This is a classic "problem" that's been "solved". – keyser May 19 '12 at 10:27
  • check here:- http://stackoverflow.com/questions/59482/whats-the-better-database-design-more-tables-or-more-columns – ravi May 19 '12 at 10:32
  • No, what's being suggested is that you have a "lecture" table in which the columns are the student name (or identifier) and some date indication and perhaps an attendance indication. In that type of schema, each student would eventually be associated with up to 48 rows of "lecture" table data. If existence of a row in the "lecture" table indicates "attendance" (no record, no attendance), you get one set of problems (hard to determine which lectures were attended), and if each row includes attendance (yes / no), you get another (larger table size). Lots of tradeoffs, lots of "it depends". – Julie in Austin May 19 '12 at 10:55
  • Your database design should support your model, not the other way around. Post your model here and we could help you design the database structure to support your model. – Chris Gessler May 19 '12 at 14:44
  • The answer here appears to be "more tables". Look for information on "database normalization" to find more on the topic. – HABO May 19 '12 at 17:58

3 Answers3

1

Do neither. You should better have one table holding your students, one for the lectures, and an intersection table for combining those.

Lucero
  • 59,176
  • 9
  • 122
  • 152
  • thanks for answering so you are saying that one table with 48 lectures and other with student and course and combining them in one to many relation in relation table?? – alternatefaraz May 19 '12 at 10:37
  • Yes, but you'll be using a many-to-many relation. – Lucero May 19 '12 at 10:38
  • "Better" is subjective. Why is it better? Is it really better in an objective sense, and if it is objectively better, what is objectively better about it? – Julie in Austin May 19 '12 at 12:34
  • 1
    Sure, better is subjective. In my opinion it is better in that it allows for change if required, the data is structured in a relational way, allowing for any SQL query required now and in the future. And this without a significant difference in the effort required for the implementation. Seems objective enough to me - end of discussion for my part. – Lucero May 19 '12 at 13:22
0

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.

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".

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.

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.

As Kyser said, study up on Relational Database theory.

Julie in Austin
  • 966
  • 5
  • 21
  • 1
    I 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. – Lucero May 19 '12 at 10:43
  • Lucero: 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". – Julie in Austin May 19 '12 at 10:51
  • 4
    And 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). – Lucero May 19 '12 at 10:59
  • 1
    There may or may not be any harm in a JOIN if that's what the actual need of the database happens to be. But if the database is simply a "check the column if student attended" database, adding additional tables, with additional columns, complexity, blah, blah, blah is a waste. Solve the problem at hand, don't just prove you know how to make giant databases with really complex relationships. – Julie in Austin May 19 '12 at 11:02
  • 1
    Oh, I'm all for getting things done, but in a way that *allows* for later change. The real cost in software development is the maintenance costs, not the creation cost. – Lucero May 19 '12 at 11:10
  • 1
    Yes, the real cost is "down the road", but if the problem is fixed and known, solve the problem. Because the other "cost" of software development is creeping featurism. That, and developers who think they know more than customers. Remember that your customer is the one paying the bills, not your desire to prove you read a bunch of books on database design. Never confuse your ego for a paying customer. – Julie in Austin May 19 '12 at 11:12
  • 4
    If you don't want to use JOINs, then don't use a relational database. – Gordon Linoff May 19 '12 at 11:12
  • @GordonLinoff: Once upon a time there was that option. I don't think I've run across a non-relational database that wasn't just a spreadsheet in 20+ years. But you're correct -- if there are no relationships involved, don't use a relational database. Use something simpler -- for this I'd suggest a spreadsheet ... – Julie in Austin May 19 '12 at 11:15
  • Julie, I'm amused by your attacks... I'm not trying to prove that I read anything. One thing my customers expect is that changes, especially enhancements, to our software can be done with a reasonable budget and in a short timeframe while maintaining high quality. This has nothing to do with databases per se, but with overall system quality and maintenance. Both over- and under-engineering have to be avoided to keep these maintenance costs low. Allowing for change while not anticipating it is a must in professional software development. – Lucero May 19 '12 at 11:19
  • 1
    @Lucero: These aren't "attacks". The OP presented a straightforward problem: A student database which reflects attendance of 48 different lectures. The OP didn't ask for a database which can include every imaginable piece of information associated with a student, lecture attendance, homework, exam, final grade, emergency contact, graduation date, alumni membership status, etc. Solve the problem at hand. Sometimes the answer is "Have you considered just using a spreadsheet for that?" – Julie in Austin May 19 '12 at 11:24
  • Julie, so how should I call this if not "attack": "Remember that your customer is the one paying the bills, not your desire to prove you read a bunch of books on database design." - You don't seem to understand my point, so be it, I think I was clear enough for others to understand, which is really the important thing here at SO. – Lucero May 19 '12 at 11:53
  • 1
    I'd call it a reminder. An "attack" would be pointing out that I was getting paid to write software while you were likely still in diapers. That would be an attack. An example of "ego" would be pointing out that several hundred million people a day use software I've written. While helping others here at SO is very important, helping developers progress from glorified typists to skilled software engineers is part of that process. I understand your point -- I've had staff that had your perspective and getting them to stop having that perspective was part of my job. Do you get mine? – Julie in Austin May 19 '12 at 12:03
  • 4
    My tuppend worth : Normalised data is more suited to processing via SQL, but de-normalised data is better suited to human viewing. As I don't intend to ask a human to answer my queries, but I do intend to ask a SQL Server to answer them, I prefer normalised data. – MatBailie May 19 '12 at 12:22
  • FWIW, what Wikipedia has to say about Normalization: http://en.wikipedia.org/wiki/Database_normalization#Objectives_of_normalization – Julie in Austin May 19 '12 at 14:01
0

Not to make more columns and Not to make more rows, just to follow the relational algebra in your database design, for more performance and oop you have to follow the standard rules, to break the rules for performance you should have experience and known reasons. your requirements should have 1 and only one tables design based on relational algebra. -- Sameh Fakoua

Sameh
  • 934
  • 1
  • 14
  • 40