Does an excessive table violate normalization rules?

Question

Here is an example of some tables that exist in a database where I work. The data isn't actually around schools, but the structure is identical.

There are four tables:

** School **
School Id, School Name

** ClubType **
ClubType Id, ClubType Name

** Club **
Club Id, School Id, ClubType Id

** Student **
Student Id, Name, Club Id

Knowing that the Club table will never have additional columns (because the real data isn't actually about school clubs),

I believe a clearly better design, eliminating the Club table to avoid joins, would be:

** School **
School Id, School Name

** ClubType **
ClubType Id, ClubType Name

** Student **
Student Id, Name, School Id, ClubType Id

Edit: We also know that each club id may only have one type. The relationship from Club to ClubType is 1 to 1.

My question is, does the first example violate some known rule of database normalization or some other mathematical principle? Or is it just a case of poor design?

Answer 1

By changing to your proposed solution you lose information from the database. The existing solution says what clubs can exist in a particular school irrespective of anyone actually being in that club at any point in time. The proposed solution requires someone to join the club before the club comes into existence (i.e. before a row is written to the database).

As a practical implication think of sign-up sheets. It's the day before term starts. The principal wants a sign-up sheet on the noticeboard for each club so students can join. It would be wasteful to print a sheet for all club types and let students join clubs which will never exists in this school. Today, before term starts, there are no students so your proposed solution will not work. The existing solution, however, allows the principal to offer, say, a soccer club but not a water polo club.

I realise you're using the analogy of school/ club as a proxy for your real situation, and you're going to have to translate all I say to the actual problem and that the comments I'm about to make may not apply. That's the price you pay for analogies. If your actual "school" can choose from all "club type" all the time then your proposed solution is adequate.

Or is it just a case of poor design?

It is not poor design. Neither is it good design. It is a design which implements some affordances but precludes others. It was written for reasons to which we do not have access. It was written with the knowledge available at the time. Likely it passed a great many tests and active, production usage.

Now, the world may have moved on since then. The business rules may have changed; the implementation team's understanding may have improved. That design may have a performance characteristic which is not acceptable on your hardware with your workload given your data. It may be appropriate to change that design.

---

Normalization is about how non-key columns depend on key columns within a single table. It shows how you can change the schema so changing a single value in the real world will update a single column in a single row within the database. It has nothing to say about implementing scenarios from the real-world problem at hand.

I understand your current Student table to mean "a person as a member of a club". For that meaning the primary key will be {student id, club id}. In your current implementation the table is not normalized because Name depends only on student id and not on club id. The normalized solution would be to change the semantics of table Student to "A person" (columns student id, name) and create a new table ClubMember with columns {student id, club id}.

The relationship from Club to ClubType is 1 to 1.

I doubt it. What are possible values of ClubType? Maybe "soccer" or "yoga"? I should think a great many schools would like to have a soccer club. Perhaps

Each Club     is-this-schools exactly one   ClubType  
Each ClubType is-offered-in   zero or more  Club

As an ERD:

ClubType --< Club >-- School

Answer 2

does the first example violate some known rule of database normalization or some other mathematical principle? Or is it just a case of poor design?

Neither. It has no obvious defects of either normalization or good design.

It sensibly models the propositions like the following:

• There's a school named 'School1'.
• There's a ClubType named 'Spanish Club'.
• School1 has a Spanish Club.
• There's a student named 'Fred' at School1 who is a member of the Spanish Club there.

The only strange thing about that model is that a Student can only be a member of one Club. It makes sense, it would just be an unusual rule for a real school.

Answer 3

From my point of view your student table is "strange" ... most schools won't limit students to 1 club.

I would make the student table as "short" as possible

** Student **
Student Id, Name

Even your column school Id is questionable because you'd usually have the entire database for ONE school - so school Id is oblivious. But that may come from your try to use an example - in my other forum we usually give the hint to use the real "example" just hiding / anonymizing data to avoid the "trap" you fell into.

Even though your goal is to reduce joins .. those are the real key to efficient database design (the "relations" in the relational database model).

So I would have at least a table

** student-club-mapping **
Student Id, Club Id

where the combo of both columns is unique and key. This allows students to be in both a language and a sports club - believe me those kinds of students exist ^^

Edit: your original layout limits students to 1 club because your table layout says so

** Student ** Student Id, Name, Club Id

this layout usually demands Student ID (as key) to be unique - so 1 student = 1 line = 1 club everything else is very bad database design (listing several club-ids in the club Id column maybe comma separated will become a nightmare when building selects for e.g student roster for a list of clubs )

Answer 4

The second design does not support multiple clubs of the same type in the same school, depending on the DB application that may even be two clubs who exist in different points in time (in our school the students journal ceased to exist an was restarted).

Connected to this there are a multitude of things which you can not represent or which will be awkward:

• empty clubs
• corresponding persons for clubs
• creation of a club (related to empty club)
• students being members of multiple clubs of the same type at different times
• You could, given only one club exists at a time make an extra table to support club name, representative, etc (awkward, but not a problem)

So the question: Which teachers supervised the clubs this student was part of? is impossible in the second formulation.

That means that the two designs cover different relationships but, given 1:1 between (club type,school) and (club) the second one should be fine.

The differences between the proposed solutions are AFAIU not related to normalization.

• What I would imagine as relevant to normalization would be not to mix student name and club membership - my intuition tells me that having a separate table representing "club memberships" consisting of student id, club id may (depending on what other tables exist in the DB) be more sustainable in keeping the database normalized in the future.

Answer 5

I see other issues with this particular database structure, but to answer the OP question directly,

No, using lookup tables such as School and ClubType does not violate any normalization rules. This is quite a standard practice to build tables around id values and have easily readable text values in a lookup table.

To address the issues I see in the database structure,

I feel that the Club table in this example should not have a school id field, and should have a name field, because a club type name and a club name can be two distinct features of a club. Further, the same club could exist at multiple schools.

Also, student should not have a club id field, because a student can be in more than one club.

Finally, this database actually needs 2 more tables to handle the many:many relationship of club:school and student:club

Answer 6

You say this isn't the real database, so we're reduced to guessing about what the real problem may be like. I understand you may not want to give away proprietary information, but couldn't you anonymize the real problem? Anyway, for the sake of discussion, I'll discuss this as if the problem you give is the real problem.

The first database design makes sense and is mostly well organized and normalized.

You can have any number of schools. This makes sense if there are multiple schools in a district, like 3 elementary schools, 2 junior highs, and a high school.

There can me many club types. I'm not sure what a club type is, but I'm thinking "sports" vs "academic" vs "cultural", maybe.

There can be many clubs within a club type. Like the sports clubs might include football, baseball, and hockey.

Clubs exist at a school. The high school may have a football club while the elementary schools do not.

A student can be a member of one or more clubs. Or can a student only be a member of one club? If a student can be in more than one club, than the student's name is repeated for every club, which is redundant data and wrong. There should be separate tables for student, with student_id and name, and club membership, with student_id and club_id. In the unlikely event that a student is only allowed to be in one club, than the design as given is correct.

You're proposed alternative creates a number of problems.

1. It fails to distinguish different clubs within a club type. If there can only be one club of each type, then okay, there is no reason to have separate club type and club tables, the two could be combined. But if there can be multiple clubs within a type, you've thrown out this information.

2. Assuming "club type" and "club" are the same thing, you have thrown away the ability to say what club/club types exist at each school. There is no way to say that the high school has a chess club but the junior high does not, for example.

3. As with the first design, if a student can be in more than one club/club type, the student's name and school id are repeated for every club.

Frankly, I think the original design is better. It's flawed, but better.