- SQL Design Decision: Should I merge these tables?
- mysql> describe chapters;
- +--------------------+------------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +--------------------+------------------+------+-----+---------+----------------+
- | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
- | school_id | int(10) unsigned | NO | MUL | | |
- | is_active | tinyint(1) | NO | | 1 | |
- | registration_date | date | YES | | NULL | |
- | state_registration | varchar(10) | YES | | NULL | |
- | renewal_date | date | YES | | NULL | |
- | population | int(10) unsigned | YES | | NULL | |
- +--------------------+------------------+------+-----+---------+----------------+
- 7 rows in set (0.01 sec)
- mysql> describe schools;
- +----------------------+------------------------------------+------+-----+---------+----------------+
- | Field | Type | Null | Key | Default | Extra |
- +----------------------+------------------------------------+------+-----+---------+----------------+
- | id | int(10) unsigned | NO | PRI | NULL | auto_increment |
- | full_name | varchar(255) | NO | MUL | | |
- | classification | enum('high','middle','elementary') | NO | | | |
- | address | varchar(255) | NO | | | |
- | city | varchar(40) | NO | | | |
- | state | char(2) | NO | | | |
- | zip | int(5) unsigned | NO | | | |
- | principal_first_name | varchar(20) | YES | | NULL | |
- | principal_last_name | varchar(20) | YES | | NULL | |
- | principal_email | varchar(20) | YES | | NULL | |
- | website | varchar(20) | YES | | NULL | |
- | population | int(10) unsigned | YES | | NULL | |
- +----------------------+------------------------------------+------+-----+---------+----------------+
- 12 rows in set (0.01 sec)
- CREATE TABLE School (
- SchoolID int unsignedNOT NULL AUTO_INCREMENT,
- CONSTRAINT PK_School PRIMARY KEY (SchoolID)
- )
- CREATE TABLE Chapter (
- ChapterID int unsigned NOT NULL,
- CONSTRAINT PK_Chapter PRIMARY KEY (ChapterID)
- CONSTRAINT FK_Chapter_School FOREIGN KEY (ChapterID) REFERENCES School (SchoolID)
- )