Get value from table which linked to another table

Sarbast

Hello,
I have two linked tables as follow:
Table 1: (Activities)
-id (PK)
-activity
-score
Table 2: (students)
-id (PK)
-name
-activity_id
-activity_score

I want to get score from Table 1 and store in Table 2 when student add an activity. Moreover, Student must select an activity and click 'Save' the 'activity_score' value stored automatically.

Colud you please help me about it.

Regards

rf1234

Rethink your data model please. You would need three tables at least I would say.

activity, student and a link between them "student_has_activity". This way a student can have multiple activities and an activity can be completed by multiple students.

Table1: activity
id, name, score

Table2: student
id, name

Table3: student_has_activity
activity_id, student_id

Whenever a student selects an activity and marks it as completed an insert into "student_has_activity" needs to be made. That's what an MJoin does in Editor by the way. If a student can do an activity multiple times and each time should "count" the model becomes a little more complicated. (And the usual MJoin logic reaches its limits, unfortunately.)

I guess you'll find lots of material on data modelling in stack overflow. It is not the focus of this forum.

Sarbast

Hi
you right about it , but this model is direct link between student and activities.

rf1234

but this model is direct link between student and activities.

But what is the purpose of this? What is the outcome that you want to achieve? If you implement a foreign key of the activity in the student table this means that a student can only have one activity and it also means the student must have one activity (unless you want to leave the foreign key with NULL values which would be very bad modelling.)

rf1234

I forgot something that I also had to learn the hard way when I started building my first application with Editor and Data Tables. In case your link table has additional attributes you reach the limits of the Mjoin method because with that you can only edit "regular" link tables containing just the two foreign keys. In your case you also want to save the activity score in the link table.

To edit that with Editor you need an Editor for just that table (you can make joins to other tables as well of course). In this excerpt from my data model you see two link tables, one that just contains two foreign keys and another one with two foreign keys plus its own id and the attribute "role".

Based on that your tables should be like this which also has the advantage that you can save multiple activity scores of one student for the same activity. If you don't want that just put a unique index on activity_id and student_id in the link table.

Table1: activity
id, name, score
 
Table2: student
id, name
 
Table3: student_has_activity
id, activity_id, student_id, score

Sarbast

Thanks alot dear,
This is very helpful.