1

I am building a database, but I am a little bit confused with my table relationships. I wish someone could help review my table relationships below for me and tell me where I am going wrong.

I have three tables, one stores students basic info ---{students_info}, the other stores their classes ---{students_class} and the other stores their positions ---{students_positions}.

students_info
 st_id | f_name | l_name
  1    | quinoo | mickel
  2    | nunoo  | gyan
  3    | kwanis | nnipa

students_class
 cl_id | class | academic_year |st_id
  13   |  A    | 2013          | 2
  32   |  B    | 2014          | 1
  73   |  C    | 2014          | 3

students_positions
 pos_id | positions | ushered_year | st_id
   54   | President |   2013       |  2
   34   | secretary |   2014       |  3

This is what I have done so far. But a friend saw my tables and its relationships and he was like, it would be best, if I created another table to contain all the ids column values in addition to the table I have. This is what he suggested:

students
 all_std | st_id | cl_id | pos_id
 01      |  1    |  32   |  null
 02      |  2    |  13   |  54
 03      |  3    |  73   |  34  

UPDATE

My friend was suggesting the additional table above (of which i think is wrong) because I was querying data from all the three tables within just a query statement using JOIN, but the data appears in duplicate. Look at the one below

SELECT 
    a.st_id
    b.class
    b.academic_year
    c.positions
FROM
    students_info a
      INNER JOIN
              students_class b
          ON
              a.st_id=b.st_id
      INNER JOIN
                students_positions c
          ON
               a.st_id=c.st_id
WHERE
      a.st_id=2;`

This is the answer i receive after executing the query

 st_id | class | academic_year | positions
   2   |  A    |    2013       | President
   2   |  A    |    2013       | President

On the contrary this is what I expect

 st_id | class | academic_year | positions
   2   |  A    |    2013       | President
3
  • 3
    You need to think about (or state, if you know them) what your candidate keys are. What is the business key of your students_class table? It looks like it may not be normalized to me. Ask your friend what is the purpose of the additional table? All of the information it contains is already available in the other tables and can be retrieved through query or view.
    – Joel Brown
    Jun 26, 2014 at 2:55
  • What questions to you want to answer using this sytem? If you can fulfil your anticipated needs without this table, omit it from your design. If your application requires the information encoded in this new table then include it. Jun 26, 2014 at 11:44
  • 1
    You need to understand the requirements before you begin the design of your database. What are the attributes, the entities, and the relationships in the "real world" of the subject matter? Once you know this, you'll have something to compare a proposed design with. Jun 26, 2014 at 12:16

1 Answer 1

1

You have 3 tables of which only first table is normalized, 2nd & 3rd are not.

  • Normalization works like this:

    students_info
    st_id | f_name | l_name | date_of_birth | address  | phone
      1   | quinoo | mickel |   1/1/1990    | nowhere1 | 1-800-1234
      2   | nunoo  | gyan   |   2/1/1990    | nowhere2 | 1-555-1234
      3   | kwanis | nnipa  |   3/1/1990    | nowhere3 | 1-555-4321
    
    classes_info
    cl_id | class | academic_year
     13   |  A    | 2014      
     32   |  B    | 2014     
     73   |  C    | 2014     
    
    students_classes
    cl_id | st_id
    13    |  2
    13    |  1
    32    |  1
    32    |  2
    73    |  3
    

Why you got it wrong? You set 3 students and 3 classes, and put each student to one class. If you have 3 students and 3 classes, and each student is going to two classes, in your example you would have duplication of rows in students_class table.

  • In basic tables, lets call them OBJECT tables (students_info, classes_info) you CAN NOT have duplication, as every object (student, class) is unique.

  • In relations tables you have relations and there you can have whatever relations you like, as there you store only id's of basic objects you relate to each other and not all other data pertaining to some object (which is stored in object table). So in students_classes table you have 5 rows, as students 1 and 2 go to two classes each, and 3rd student is going only to one class.

  • Having 300 students and 20 classes, tables for students and classes whould have 300 and 20 rows respectively, and relations table students_classes would have as many rows as needed, e.g. if every student must take 5 classes, and can take max 10 classes, students_classes table could have from 1500 to 3000 rows (for one academic year).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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