Home » Other » General » Need Guideline in defining a Table Structure (Oracle 8i, Forms 6i)
icon5.gif  Need Guideline in defining a Table Structure [message #404319] Thu, 21 May 2009 05:56 Go to next message
itech
Messages: 173
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

Hi,

I need some guideline in defining a table structure for a school management system.

Module is Students Attendance.

there will be approx 2000 active students (in admission module)
in Attendance module daily attendance will be maintained (once for whole day, not for each subject).

So do u suggest that it's right to create 2000 records in the attendance Table for each day ?

or any alternate solution ?

another in my mind is to create a 31 column table with month wise records for 2000 students, some intelligent logic will be required to use the clms corectly.

what approach is better. and resource efficient.

thanking in anticipation.

==========================================================
Moderator pls move to specific section if non suitable here.

[Updated on: Thu, 21 May 2009 05:57]

Report message to a moderator

Re: Need Guideline in defining a Table Structure [message #404322 is a reply to message #404319] Thu, 21 May 2009 06:11 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Instead of recording attendance, you could record non-attendance
Re: Need Guideline in defining a Table Structure [message #404324 is a reply to message #404319] Thu, 21 May 2009 06:25 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

I think both of your solutions has advantages and disadvantages,

1. Having 2000 Records per day is not a big deal Oracle Database,Suppose you have the following table structure,

Day_No, Stud_Name, Present_Absent, General_Leave.


Quering would be very easy i believe.

2. If you have 31 columns for a student , then per month you will have 2000 records only, but everyday you should UPDATE the table,

Quote:
Month_Name, Student_Name,Day1,Day2...Day31,Leave_Day


With this structure Quering would be very difficult,Assume if you want to see "on what day the student was abscent"

Re: Need Guideline in defining a Table Structure [message #404330 is a reply to message #404324] Thu, 21 May 2009 06:45 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
A column per day would violate 3NF. Bad idea.
Re: Need Guideline in defining a Table Structure [message #404385 is a reply to message #404322] Thu, 21 May 2009 10:41 Go to previous messageGo to next message
itech
Messages: 173
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

pablolee wrote on Thu, 21 May 2009 16:11
Instead of recording attendance, you could record non-attendance


Then what about the reports detail and summarized reports of each class, section,

a view can be created, but students sessions also ends, students get struck off, school leaving, passing out etc...

what you recommend in that case...
Re: Need Guideline in defining a Table Structure [message #404386 is a reply to message #404324] Thu, 21 May 2009 10:42 Go to previous messageGo to next message
itech
Messages: 173
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

yes, querying in 31 cloumns will be difficult.

ashoka_bl wrote on Thu, 21 May 2009 16:25
I think both of your solutions has advantages and disadvantages,

1. Having 2000 Records per day is not a big deal Oracle Database,Suppose you have the following table structure,

Day_No, Stud_Name, Present_Absent, General_Leave.


Quering would be very easy i believe.

2. If you have 31 columns for a student , then per month you will have 2000 records only, but everyday you should UPDATE the table,

Quote:
Month_Name, Student_Name,Day1,Day2...Day31,Leave_Day


With this structure Quering would be very difficult,Assume if you want to see "on what day the student was abscent"



Re: Need Guideline in defining a Table Structure [message #404412 is a reply to message #404385] Thu, 21 May 2009 13:24 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Quote:
Then what about the reports detail and summarized reports of each class, section,


Sorry, I have no idea what you just said there.
Quote:
a view can be created, but students sessions also ends, students get struck off, school leaving, passing out etc...

what you recommend in that case...


I reccomend that if you are trying to build an entire system rather than the single table that your original question alluded to, then you either learn a bit about designing OLTP systems and The forms of normalization or you pay someone to design your database or you do an online search for template databases of the sort that you require. I am not going to sit and guess as to your requirements, suggest solutions only for you to post back with:
"ahh, but what about this piece of information I decided not to include"
Re: Need Guideline in defining a Table Structure [message #404749 is a reply to message #404412] Sat, 23 May 2009 04:13 Go to previous messageGo to next message
itech
Messages: 173
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

i meant, that if only marking the absentees, how can i create the summarized & detailed reports of present / absentees, an individual student's record, whole class / section / level / campus wise reports. etc...

any idea for that ?

for example, there are 30 students in class 6 - A (similarly in 6-b, 6-c, and so on with other classes)

if i only record the the absent students, then after a month the management staff wants to get the summarized reports showing all students totals., and detailed report for each day of all students.

hope you got my point.

pablolee wrote on Thu, 21 May 2009 23:24
Quote:
Then what about the reports detail and summarized reports of each class, section,


Sorry, I have no idea what you just said there.
Quote:
a view can be created, but students sessions also ends, students get struck off, school leaving, passing out etc...

what you recommend in that case...


I reccomend that if you are trying to build an entire system rather than the single table that your original question alluded to, then you either learn a bit about designing OLTP systems and The forms of normalization or you pay someone to design your database or you do an online search for template databases of the sort that you require. I am not going to sit and guess as to your requirements, suggest solutions only for you to post back with:
"ahh, but what about this piece of information I decided not to include"

Re: Need Guideline in defining a Table Structure [message #404915 is a reply to message #404749] Mon, 25 May 2009 07:35 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
The first thing that you must do is get as complete a list of requirements as you can. Use that list to create a list of information that you will need to either store, or be able to calculate. Then use that list to create a list of columns that you will need. You can then start to consider your structure at this point.
Re: Need Guideline in defining a Table Structure [message #405108 is a reply to message #404319] Tue, 26 May 2009 06:59 Go to previous messageGo to next message
itech
Messages: 173
Registered: May 2008
Location: Fsd, Pakistan
Senior Member

i've developed the module,

the master table holds the attendance date (with logged in user information & terminal)

the detail table is just to select the student, its roll number, campus id, class id, section id, and attendance status (as when the student will be promoted to next class, the history should be maintained)

the user only inputs the absent students, when before saving there is an option that should the system automatically mark other students as present. then in the post forms commit trigger, the system add the record of remaining students as present in the detail table, (how ever the records can be updated next time via querying)
Re: Need Guideline in defining a Table Structure [message #406203 is a reply to message #405108] Tue, 02 June 2009 15:26 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Just a word on de-normalized tables (the 31 column idea). It's acceptable to break 3NR by have repeating fields when the number of those repeating fields is fixed (months of year, days of week, number of holes in a regular game of golf etc are some examples). Number of days in month - well 31 is close enough to be considered.

The good thing about repeating fields is it's easy to ensure that each of the 31 days has a value. It's much harder to find missing records in 3NR when a full up to 31 separate records is required.

Querying over 31 repeating fields becomes more difficult to answer many questions though e.g. list students who missed more than 1/2 of their classes...

As a practical matter, if your input and reporting screens are going to have 31 columns, it'll be easier to code because your presentation format matches your storage structure. This statment will probably draw plenty of valid critisism though...

With all the pros & cons I usually avoid repeating fields.
Previous Topic: Mismatch Between Body/Spec PL/SQL package
Next Topic: XML file opening error
Goto Forum:
  


Current Time: Thu Mar 28 14:37:37 CDT 2024