[Smp-developers] Re: The Database...is done!

[James Y. <aj...@se...>]

Hello,

I gave up on installing MySQL until next weekend at least and played
around with the tables in PostgreSQL a bit... cool stuff.  Anyway, I
have some comments and questions:

> #
> # Table structure for table 'address'
> #
> 
> DROP TABLE IF EXISTS address;
> CREATE TABLE address (
>   address_id varchar(10) NOT NULL default '',
>   house_number int(6) default NULL,
>   street varchar(40) default NULL,
>   city varchar(30) default NULL,
>   state char(3) default NULL,
>   zip varchar(10) default NULL,
>   PRIMARY KEY  (address_id)
> ) TYPE=MyISAM;
> 
> #
> # Table structure for table 'class_history'
> #
> 
> DROP TABLE IF EXISTS class_history;
> CREATE TABLE class_history (
>   student_id varchar(10) default NULL,
>   class_id int(7) default NULL,
>   grade char(3) default NULL,
>   uid int(10) NOT NULL auto_increment,
>   PRIMARY KEY  (uid),

What was uid again?

>   KEY student (student_id),
>   KEY class (class_id)
> ) TYPE=MyISAM;
> 
> #
> # Table structure for table 'class_info'
> #
> 
> DROP TABLE IF EXISTS class_info;
> CREATE TABLE class_info (
>   id int(7) NOT NULL default '0',
>   number varchar(9) default NULL,
>   instructor varchar(60) default NULL,
>   time varchar(10) default NULL,

One thing: "time" is an SQL reserved word; if you're interested
in portability you should call this field something else but if
that's not a big deal to you, then "time" should still be fine.

>   semester varchar(8) default NULL,
>   year int(4) default NULL,

I think "year" is a reserved word too... see above comment.

>   PRIMARY KEY  (id)
> ) TYPE=MyISAM;
> 
> #
> # Table structure for table 'course_info'
> #
> 
> DROP TABLE IF EXISTS course_info;
> CREATE TABLE course_info (
>   number varchar(9) NOT NULL default '',
>   credit decimal(3,1) default NULL,
>   name varchar(255) default NULL,
>   description text,
>   PRIMARY KEY  (number)
> ) TYPE=MyISAM;
> 
> #
> # Table structure for table 'current_class'
> #
> 
> DROP TABLE IF EXISTS current_class;
> CREATE TABLE current_class (
>   student_id varchar(10) default NULL,
>   class_id int(7) default NULL,
>   ua int(3) default NULL,
>   ea int(3) default NULL,

Just a thought: since table definitions are potent documentation,
you may want to call fields "explained_absences", "class_id" etc.
instead of "ea", "id" etc.

>   grade1 char(3) default NULL,
>   grade2 char(3) default NULL,
>   grade3 char(3) default NULL,
>   grade4 char(3) default NULL,
>   grade5 char(3) default NULL,
>   grade6 char(3) default NULL,
>   grade7 char(3) default NULL,
>   grade8 char(3) default NULL,
>   comments int(5) default NULL,
>   uid int(10) NOT NULL auto_increment,
>   PRIMARY KEY  (uid),

What was uid on this table?

>   KEY cur_student (student_id),
>   KEY cur_class (class_id)
> ) TYPE=MyISAM;
> 
> #
> # Table structure for table 'parents'
> #
> 
> DROP TABLE IF EXISTS parents;
> CREATE TABLE parents (
>   parent_id varchar(10) NOT NULL default '',
>   title varchar(4) default NULL,
>   first varchar(20) default NULL,
>   middle varchar(20) default NULL,
>   last varchar(20) default NULL,
>   suffix varchar(4) default NULL,
>   h_phone varchar(16) default NULL,
>   w_phone varchar(16) default NULL,
>   w_ext varchar(5) default NULL,
>   address_id varchar(10) default NULL,
>   PRIMARY KEY  (parent_id)

I was going to point out that address_id need not be a key, but
you already noticed that so that's good.

> ) TYPE=MyISAM;
> 
> #
> # Table structure for table 'relationships'
> #
> 
> DROP TABLE IF EXISTS relationships;
> CREATE TABLE relationships (
>   student_id varchar(10) NOT NULL default '',
>   parent_id varchar(10) default NULL,
>   relation char(1) default NULL,

Isn't it weird how "Mother" starts with "M" and "Father" starts with
"F", but when you see a bunch of "M"'s and "F"'s in a table, you think
male/female, which is the exact opposite of what they mean here?
Weird.  Anyway...

>   sequence int(1) default NULL,

Playing around with some test queries, I thought that it might be
better if I backed off from what I thought before and called this
field "primary_contact", and make it a char(1) containing either
"Y" or "N".  Probably be more meaningful on forms anyway.
What do you think?

>   PRIMARY KEY  (student_id)
> ) TYPE=MyISAM;
> 
> #
> # Table structure for table 'students'
> #
> 
> DROP TABLE IF EXISTS students;
> CREATE TABLE students (
>   id varchar(10) NOT NULL default '',
>   first varchar(20) default NULL,
>   middle varchar(20) default NULL,
>   last varchar(20) default NULL,
>   home_phone varchar(16) default NULL,
>   PRIMARY KEY  (id)

Also a key of the first six (or so) characters of the last name,
since I think you mentioned that some of the queries will be by
last name.

> ) TYPE=MyISAM;
> 

Thanks,
- James
A. James Yolkowski  *  aj...@se...  *  http://www.sentex.net/~ajy/
  "A lost ounce of gold may be found, a lost moment of time never."