SQL Design Decision: Should I merge these tables?

Question

I'm attempting to design a small database for a customer. My customer has an organization that works with public and private schools; for every school that's involved, there's an implementation (a chapter) at each school.

To design this, I've put together two tables; one for schools and one for chapters. I'm not sure, however, if I should merge the two together. The tables are as follows:

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)

(Note that these tables are incomplete - I haven't implemented foreign keys yet. Also, please ignore the varchar sizes for some of the fields, they'll be changing.)

So far, the pros of keeping them separate are:

1. Separate queries of schools and chapters are easier. I don't know if it's necessary at the moment, but it's nice to be able to do.
2. I can make a chapter inactive without directly affecting the school information.
3. General separation of data - the fields in "chapters" are directly related to the chapter itself, not the school in which it exists. (I like the organization - it makes more sense to me. Also follows the "nothing but the key" mantra.)
4. If possible, we can collect school data without having a chapter associated with it, which may make sense if we eventually want people to select a school and autopopulate the data.

And the cons:

1. Separate IDs for schools and chapters. As far as I know, there will only ever be a one-to-one relationship between the two, so doing this might introduce more complexity that could lead to errors down the line (like importing data from a spreadsheet, which is unfornately something I'll be doing a lot of).
2. If there's a one-to-one ratio, and the IDs are auto_increment fields, I'm guessing that the chapter_id and school_id will end up being the same - so why not just put them in a single table?
3. From what I understand, the chapters aren't really identifiable on their own - they're bound to a school, and as such should be a subset of a school. Should they really be separate objects in a table?

Right now, I'm leaning towards keeping them as two separate tables; it seems as though the pros outweigh the cons, but I want to make sure that I'm not creating a situation that could cause problems down the line. I've been in touch with my customer and I'm trying to get more details about the data they store and what they want to do with it, which I think will really help. However, I'd like some opinions from the well-informed folks on here; is there anything I haven't thought of? The bottom line here is just that I want to do things right the first time around.

Answer 1

I think they should be kept separate. But, you can make the chapter a subtype of a school (and the school the supertype) and use the same ID. Elsewhere in the database where you use SchoolID you mean the school and where you use ChapterID you mean the chapter.

CREATE TABLE School (
   SchoolID int unsigned NOT 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)
)

Now you can't have a chapter unless there's a school first. If such a time occurred that you had to allow multiple chapters per school, you would recreate the Chapter table with ChapterID as identity/auto-increment, add a SchoolID column populated with the same value and put the FK on this one to School, and continue as before, only inserting the ID to SchoolID instead of ChapterID. If MySQL supports inserting explicit values to an autoincrement column, then making it SchoolID autoincrement ahead of time could save you trouble later (unless switching a regular column to autoincrement is supported in which case no issues there).

Additional benefits of keeping them separate:

• You can make foreign key relationships directly with SchoolID or ChapterID so that the data you're storing is always correct (for example, if no chapter exists yet you can't store related data for such a thing until it is created).
• Querying each table separately will perform better as the rows don't contain extraneous information.
• A school can be created with certain required columns, but the chapter left uncreated (temporarily). Then, when it is created, you can have some NOT NULL columns in it as well.

Answer 2

Another reason to keep them separate is if the amount of information about the two entities combined would make the length of the records longer than the database backend can handle. One-to_one tables are often built to keep the amount of data that needs to be stored in a record down to an appropriate size.

Further is the requirement a firm 1-1 or is does it have the potential to be 1-many? If the second, make it a separate table now. Id there the potential to have schools without chapters? Again I'd keep them separate.

And how are you intending to query this data, will you generally need the data about both the chapter and school in the same queries, then you might put them in one table if you are sure there is no possibility of it turning into a 1-many relationship. However a proper join with the join fields indexed should be fast anyway.

I tend to see these as separate entities and would keep them separte unless there was a critcal performance problem that would lead to putting them to gether. I think that having separate entities in separate table from the start tends to be less risky than putting them together. And performance would normally be perfectly acceptable as long as the indexing is correct and may even be better if you don't normally need to query data from both tables all the time.

Answer 3

keep them separate.

they may be 1-1 currently... however these are clearly separate concepts.

will they eventually want to input schools which do not have chapters? perhaps as part of a sales lead system?

can there really only be one chapter per school or just one active chapter ? what about across time? is it possible they will request a report with all chapters in the past 10 years at x school ?

Answer 4

You said the links will always be 1 to 1, but does a school always have a chapter can it change chapters? If so, then keeping chapters separate is a good idea.