I am trying to create a little database for any customer. My customer comes with an organization that actually works with private and public schools for each school that's involved, there's an implementation (an instalment) each and every school.

To create this, I have come up with two tables one for schools and something for sections. I am unsure, however, basically should merge the 2 together. The tables are the following:

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)

(Observe that these tables are incomplete - I've not implemented foreign secrets yet. Also, please disregard the varchar dimensions for a few of the fields, they will be altering.)

To date, the professionals of keeping them separate are:

  1. Separate queries of schools and sections are simpler. I'm not sure if it's necessary right now, but it's nice to have the ability to do.
  2. I can produce a chapter inactive without directly affecting the school information.
  3. General separation of information - the fields in "sections" are proportional to the chapter itself, not the college that is available. (I love the organization - it will work better in my experience. Also follows the "nothing the answerInch mantra.)
  4. If at all possible, we are able to collect school data without getting an instalment connected by using it, which might make sense when we eventually want people to choose a college and autopopulate the information.

And also the cons:

  1. Separate IDs for schools and sections. So far as I understand, there is only going to be a 1-to-one relationship between your two, so carrying this out might introduce more complexity that can lead to errors down the road (like posting data from the spreadsheet, that is unfornately something I'm going to be doing lots of).
  2. If there is a one-to-one ratio, and the IDs are auto_increment fields, I am speculating the chapter_id and school_id will finish up being exactly the same - so why wouldn't you just insert them in just one table?
  3. From things i understand, the sections aren't really identifiable on their own own - they are certain to a college, and as a result ought to be a subset of the school. Whenever they be separate objects inside a table?

At this time, I am leaning towards keeping them as two separate tables it appears as if the professionals over-shadow the cons, but I wish to make certain that I am not developing a situation that may create problems down the road. I have been in contact with my customer and I am looking to get more particulars concerning the data they store and what they need related to it, that we think will truly help. However, I would like some opinions in the well-informed folks on here's there anything I've not considered? Tha harsh truth here's exactly that I wish to do things right to begin with.