Members and Studies application

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
xionhack
Forum Contributor
Posts: 100
Joined: Mon Nov 10, 2008 9:22 pm

Members and Studies application

Post by xionhack »

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.
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

Re: Members and Studies application

Post by buckit »

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
xionhack
Forum Contributor
Posts: 100
Joined: Mon Nov 10, 2008 9:22 pm

Re: Members and Studies application

Post by xionhack »

But when its looking through so many records, wouldnt it make it slow?
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

Re: Members and Studies application

Post by buckit »

how many records are you talking about? you say 1000 members... how many studies per member?
xionhack
Forum Contributor
Posts: 100
Joined: Mon Nov 10, 2008 9:22 pm

Re: Members and Studies application

Post by xionhack »

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?
Post Reply