Hello. I have a question.
I am making an application that shows if some "members" have done some "studies". There are 50 set studies. This is the way I have it set up:
3 Tables --->
Member:
member_id, member_name
Studies:
study_id, study_name
member_studies:
member_id, study_id, teacher, comments, date
In the "member" table I actually have about 50 other fields, I just didnt put them because they were not needed. My concern is, because I have more than 1000 members, If I want to know how many people did for example the "study 1" then, It will do a search through all the ones that have the study_id = 1, then check which name is to the "member_id" and which study_name goes for the "study_id". My problem is that I think that would take a long time. Is it better if I just put those fields for each lesson in the "members" table (the problem with that one, is that I will have more than 200 fields). I dont know If everything is clear, but please let me know if its not, thank you.
Members and Studies application
Moderator: General Moderators
Re: Members and Studies application
you're fine with that layout. it would be a fairly simple SQL statement to grab only what you need.
SELECT m.member_name, s.study_name FROM member m, studies s, member_studies ms WHERE ms.study_id = 1 AND m.member_id = ms.member_id AND s.study_id = ms.study_id
that should return a recordset that has the member_name and study_name of all studies with study_id of 1
SELECT m.member_name, s.study_name FROM member m, studies s, member_studies ms WHERE ms.study_id = 1 AND m.member_id = ms.member_id AND s.study_id = ms.study_id
that should return a recordset that has the member_name and study_name of all studies with study_id of 1
Re: Members and Studies application
But when its looking through so many records, wouldnt it make it slow?
Re: Members and Studies application
how many records are you talking about? you say 1000 members... how many studies per member?
Re: Members and Studies application
IT would be 50 studies per member. But maybe I would want to know in one moment who havent done a study or who have done it. Would that query be slow?