|
From: James Y. <aj...@se...> - 2001-08-06 22:39:16
|
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." |