2uzhan.com
Advertisement
Now Place:2uzhan.com » mySQL db Table Set Up Help

mySQL db Table Set Up Help

PHP @ December 8, 2003   Views:0

I have an assignment table in my database. Teachers can enter weekly assignments for their high school classes and then parents can retrieve their students assignment sheet for the week. We have 8 periods, so there can be as many as 8 sections for a class, though most only have 3 sections and a few have as many as 6 sections. We have a fall break in October, and I'm thinking I might want to change this whole scripts. It was one of the first scripts I wrote when I was learning PHP and there's lots of redundant code that I'd like to "fix". Here's the table structure:

PHP Code:

assignment
Field       Type        Attributes  Null  Default   Extra 
assignid    int(10)     UNSIGNED    No              auto_increment  
courseid    varchar(20)             No      
S01A        varchar(10)             Yes   NULL    
S02A        varchar(10)             Yes   NULL    
S03A        varchar(10)             Yes   NULL    
S04A        varchar(10)             Yes   NULL    
S05A        varchar(10)             Yes   NULL    
S06A        varchar(10)             Yes   NULL    
S07A        varchar(10)             Yes   NULL    
S08A        varchar(10)             Yes   NULL    
assigndate  date                    Yes   0000-00-00    
assigndesc  text                    Yes   NULL    
updatedby   varchar(50)             Yes   NULL    
lastupdate  timestamp(14)           Yes   NULL 

This will put one assignment on each date.
I was thinking that this takes up a lot of space. Since the teachers can put out multiple weeks, I run a purge script that clears old rows out at the end of the 9 week grading period.
Would something like this work better::

PHP Code:

assignment
Field       Type        Attributes  Null  Default   Extra 
assignid    int(10)     UNSIGNED    No              auto_increment  
courseid    varchar(20)             No      
S01A        varchar(5)              Yes   NULL    
S02A        varchar(5)              Yes   NULL    
S03A        varchar(5)              Yes   NULL    
S04A        varchar(5)              Yes   NULL    
S05A        varchar(5)              Yes   NULL    
S06A        varchar(5)              Yes   NULL    
S07A        varchar(5)              Yes   NULL    
S08A        varchar(5)              Yes   NULL    
startweek   date                    No    
MonDesc     text                    Yes   NULL    
TueDesc     text                    Yes   NULL  
WedDesc     text                    Yes   NULL  
ThuDesc     text                    Yes   NULL  
FriDesc     text                    Yes   NULL  
updatedby   varchar(50)             No    
lastupdate  timestamp(14)           No 

Where startweek would be the Monday for the week. I would also like a better way of handeling the sections (S01A-S08A). Any ideas or suggestions would be greatly appreciated.

--------------Solutions-------------

// you want to break up your table, like so:

table section

section_id int(10),
section_name varchar(20)

table assignment

Field Type Attributes Null Default Extra

assignid int(10) UNSIGNED No auto_increment
courseid varchar(20) No
section_id int(10) No
assigndate date Yes 0000-00-00
assigndesc text Yes NULL
updatedby varchar(50) Yes NULL
lastupdate timestamp(14) Yes NULL

I'm not sure that will work. I have an course table:

PHP Code:

coursetable
Field       Type     Attributes Null Default  Extra 
id          int(10)  UNSIGNED   No            auto_increment  
courseid    varchar(15)         No      
sectionid   varchar(10)         No      
period      tinyint(4)          No   0    
room        varchar(6)          No      
staffid     int(11)             No   0    
coursename  varchar(50)         No      
subject     varchar(50)         No      
grade       varchar(15)         No 

and a schedule table:

PHP Code:

scheduletable
Field     Type     Attributes Null Default Extra 
id        int(11)  UNSIGNED   No           auto_increment  
courseid  varchar(30)         No      
sectionid varchar(10)         No      
studentid int(11)             No   0 

and a student table

PHP Code:

HS_studenttable
Field      Type     Attributes Null Default Extra 
studentid  int(10)  UNSIGNED   No           auto_increment  
lastname   varchar(20)         No      
firstname  varchar(20)         No      
grade      varchar(5)          No      
bg         char(3)             Yes   NULL 

with all the field names matching up to. I am mainly wanting to know if it's better to have 5 assignments (Monday-Friday) on one table row or to have each assignment on it's own table row.

With the sections, say with 9th Grade Biology, the same teacher teaches all three sections, but say with 11th grade English, one teacher may teach sections 01A and 03A and another teacher teaches section 02A. Even if a teacher has 3 sections of one subject, section 02A might be ahead or behind the other sections, so the teacher should be able to allow for that. However, if all 3 sections have the same homework, then I don't want to have identical assignment entries for each section. Right now the sections are set as VARCHAR. I know there's the enum type that would allow me to turn these on and off, like radio buttons, but is there a type that would allow for multiple entries, like checkboxes?

You can achieve what you described with the table model I gave you. As a general rule, you want to keep your tables in normal form. at least 2nd normal form, but the standard is 3rd normal form.

See http://www.phpbuilder.com/columns/ba...31.php3?page=1 for a good, clear article on this. I think this will clear things up for you.

With one assignment that can be assigned to many sections, and one section being able to have several assignments, you have an M:N relationship. Sounds to me like you need an associative table:

you'll have 3 tables: assignments, sections, and section_assignments(section_id,assignment_id).

If an assignment goes for 3 sections (say the teacher can select several sections to be assign the assignment to from a mutiple select form item), you would have something liek this:

$insert1=mysql_query("insert into assignments .....your insert code");

$result=mysql_query("select LAST_INSERT_ID() as ID from assignments");

$assign_id=mysql_fetch_array($result['ID']);

(if the section selection comes from a multiple select menu, it will be returned as a comma-delimited list, so you must convert it to an array in php)

$section_array=explode(",",$menu_sections_list);

foreach($sections as $key=>$value) {
insert2=mysql_query("insert section_assignments (section_id,assignment_id)
values ('$value','$ID')");
}

I read the article you sent and I understand about normilazation of tables. I just don't see how adding a section table would help. What I think I need is to change those 8 section fields to a SET field type. The information that is stored in the section field, along with the courseid will be the key to finding the right entry in the student table.

Actually that's backwards. When a student logs in to get assignments, they type their first and last name, their grade, and the starting week. I find them in the student table and then use their studentid number to read the schedule table SELECT * FROM schedule table WHERE studentid = '$studentrow['studentid']'. Then I run through these 8 results and pull the courseid and sectionid from the assignment table. It's a little more complicated than that, and I do hit the coursetable between the schedule table and the assignment table.

It works, but it isn't very pretty. I'm mainly looking for a more efficient manner to do this.

Thanks for your suggestions. I'm self taught, so haven't got it all perfectly figured out yet. I'm taking a Hands On Technology Training class in a few weeks, so hopefully that will clear up some of my confusing.

Tags:
© 2018 2uzhan.com Contact