When a specific prize is picked by a student - is it then no longer available to other students?
Prize_control is basically type of the prizes available to studens like bagpacks, towel and waterbottle and any student can pick up anyhting as far as it is within his buying capatity( I mean as far as he ahs enough points to acquire that prize).
Then you should use three tables intead of just two: Students, Prizes and Awards - the last one being a join table between Students and Prizes.
Thats what my problem is. How can I make a join between student and prize when there is no common field between them.
Student_ info Prize_control
last name Points
Here we can see that both tables have points field but they mean completely different in this context point in student info is the points earned by a particular student and point in prize control is points that a prize is worth of. and moreover none of them is a primary key of that table.
I think your problem is that the relationship between Students and Prizes is many-to-many (if I assume correctly that a student can "acquire" more than one prize). Therefore you should use a third table as the join. The fields you need for this are (roughly):
• StudentID (auto-entered serial)
... (more fields that describe the student)...
• PrizeID (auto-entered serial)
• Value (the "price" in points)
• Value (lookup from Prizes, in case the "price" changes in the future)
and the two relationships:
Students:: StudentID = Awards:: StudentID
Prizes:: PrizeID = Awards:: PrizeID
BTW, i believe that points earned by the student should also be recorded in the Awards table (credit instead of debit), so that you can easily get the points balance.
Thank you very mcuh . That seems to be working and I think that would be a final solution for me . Thank you very very muck for your help.