1

I am new to databases. I am using MySql.

What I have is a student system which stores rules for students in a school.

Each student may or may not be a part of a group (Soccer, Guitar, etc).

Different rules are imposed on individual students and also on groups.

Student table:

|Student_id|Student_name|
|----------|------------|
|         1|Jack        |
|         2|John        |
|         3|Bob         |
|----------|------------|

Group table:

|Group_id|Group_name|
|--------|----------|
|       1|Soccer    |
|--------|----------|

Student_group_table:

|Student_id|Group_id|
|----------|--------|
|         1|       1|   --Jack plays soccer
|         2|       1|   --John also plays soccer
|----------|--------|   

Rules (dirty design):

|id|entity_id|entity_type|rule      |
|--|---------|-----------|----------|
| 1|        3|student    |Bus at 7am| --Bob has to catch bus at 7am
| 2|        1|group      |Shoes     | --All soccer students must bring shoes
|--|---------|-----------|----------|

I would appreciate a cleaner alternative to this dirty design.

  • 4
    How do you define "cleaner"? I see 2 primary tables (student & the poorly named 'group'), a middle of a many-to-many relationship table, and a details table ("rules"). Where's the problem? – Dan Pichelman Jul 20 '15 at 14:06
  • 1
    Don't split up students and groups. They are both "entities" that can have rules applied to them. Therefore they should be similar in feature. The only difference between a student or a group (regarding "rules") is that a student is an individual. And the benefit to this is a recursive ownership of entities owning entities (i.e. a student entity part of a group identity), and even more levels such as groups owning groups (soccer team entity has a parent entity of sports team group entity). All entities would have a common parent at the highest level of the school. My $0.02. – Thomas Stringer Jul 20 '15 at 14:06
  • @Dan the problem is storing thousands of rules in the rules table and the join depends on the string comparison of type=student/group to either join from student table or group table, which seems inefficient – user3215614 Jul 20 '15 at 14:12
  • 3
    @user3215614 "seems" inefficient or "is" inefficient? Have you stuffed the tables full of a few tens of thousands of records for testing and measured? It might perform better than you think. – user22815 Jul 20 '15 at 14:14
  • 1
    @Snowman It also doesn't make sense in my design perspective as i also want to apply constraint on the entity_id (edited) to be a foreign key of either student or group, which would restrict junk inserts – user3215614 Jul 20 '15 at 14:30
4

An alternative to @Thomas comment (which is viable, although imo recursive foreign keys add too much complexity if you do not need the additional power they give) would be to create separate tables for each foreign-key type (one for students, one for groups), allowing for enforceable foreign keys:

Rules:

|id|rule      |
|--|----------|
| 1|Bus at 7am| -- bus has to be caught at 7am
| 2|Shoes     | -- shoes have to be brought
|--|----------|

student_rules_table:

|Student_id|rule_id |
|----------|--------|
|         3|       1| --Bob has to follow rule 1
|----------|--------|   

group_rules_table:

|Group_id  |rule_id |
|----------|--------|
|         1|       2| --All soccer students have to follow rule 2
|----------|--------|

This would also result in a lower number of distinct rules, since these are no longer dependent on any connected entity.

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.