8

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?

  • 5
    Your example may not represent your problem well. A clubType is presumably something like "foreign language club" while a club is something like "Spanish club". Eliminating clubType just because club won't have additional columns doesn't make sense. If you know that you would never group more than 1 club together then eliminating clubType would make sense. But the issue may be that your example isn't a good representation of your actual problem/ thinking... – Justin Cave Oct 6 at 22:35
  • @JustinCave that's a good point and I've updated my question to to address it, assuming I've understood you correctly. – Burgan Oct 6 at 22:47
  • 4
    With your design we have no way indicating a club exists if there are no students in it. In a real world use case that is a legitimate situation that could arise (for instance, creating a club before students have started signing up). Is this a problem with your actual problem? (Things like this are why it is very difficult to answer questions where we don't know the actual problem.) – Willem Renzema Oct 6 at 23:55
  • 4
    What is an "excessive table"? – RonJohn Oct 7 at 20:39
  • 1
    You don't give a specification, so we can't tell you what design is appropriate. PS Time to follow a published academic textbook on information modelling, the relational model & DB design & querying. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) – philipxy Oct 8 at 1:07
19

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
  • 3
    After considering your answer for some time, I now better understand the trade offs between my two proposed designs. My club analogy now seems fundamentally broken, but nonetheless your comments largely apply. The key insight you provided was that empty clubs cannot exist in the second example that I provided. – Burgan Oct 7 at 1:02
4

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.

1

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 )

  • Can you explain to me, please, why the design would "limit students to 1 club"? – Michael Green Oct 7 at 15:34
  • 1
    In his 1970 paper "A Relational Model of Data for Large Shared Data Banks" Codd says "The term relation is used here in its accepted mathematical sense" i.e. sets of tuples. It does not have a meaning related to the everyday use of "relationships" in this context. – Michael Green Oct 7 at 16:02
  • 2
    @MichaelGreen Given that you dedicated a section of your own answer explaining how to normalize the Student tables & suggest the addition of a student-club table, I'm confused by your question. Are questioning the claim that the design limits students to 1 club, or are you just suggesting the answer would be improved by spelling it out? – Mr.Mindor Oct 8 at 0:01
  • 1
    @Mr.Mindor I'm questioning the claim. I read eagle275's first sentence as an assertion that the OP's design does, in fact, limit students to one club. I see nothing in either the current nor in the proposed schema that supports that assertion. The foreign key arrangements don't enforce it and there's no mention of constraints at all. While it would be possible to create a unique constraint on Student.StudentId (OP's answer) or ClubMember.StudentId (my proposal) it would be unusual. Its existence could not be inferred from the question or normal practice. – Michael Green Oct 9 at 0:48
  • @Mr.Mindor I'm quite prepared to believe I misread that sentence or the question, however. That's why I asked for it to be explained to me. If there is an explanation and I've misunderstood it's likely others will, too. So the answer would benefit from the OP spelling it out. – Michael Green Oct 9 at 0:48
0

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.
0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.