Clever Geek Handbook

Database design to meet the needs of 3NF

I want to create a db, but I am having some difficulties. I have

Students
id, name, group, year of study, specialization, list of scolarships

and

Scolarships
id, name, description, duration, list of applicants

From what I've read so far, I know that there cannot be a list stored in a cell. So my question is, how could I represent these two lists from every table I have so far?

I was thinking about creating another table, but I don't know how to create it. Please help me with some suggestions.

+3


source to share


2 answers


The trick here is to create a third table (which I call Bridge

) that contains the relationship between students and their scholarships. This table will contain two foreign keys pointing to tables Students

and Scholarships

. When you want to get a list of students and their scholarships, you will use this table to join the two normalized tables in the original problem.

Students (id, name, group, year of study, specialization)           id is a primary key
Scholarships (id, name, description, duration)                      id is a primary key
Bridge (student_id, scholarship_id)                                 both are foreign keys


Then, when you want to get a list of students with their scholarships, you make a request JOIN

like this:

SELECT *
FROM
    Students st
    INNER JOIN Bridge b ON st.id = b.student_id
    INNER JOIN Scholarships sc ON b.scholarship_id = sc.id;
+1


source


Perhaps it:



Students(id,name,group,year of study, spec)
    Scolarships(id,name,description,duration)
    Applicants(scolarshipID,studentID)
0


source






More articles:


All Articles