Best way to design these tables and query them?

[GuitarGod]

Hi all,

 

I'm after some advice about these tables and whether I'm using them in the most efficient way.

 

Currently I have 4 tables; Students, Schools, Administrators and School members.

 

The Students table is simply a list of students with an 'admin_id' field - which relates to an administrator in the Administrators table who created the student (the student may not yet be a member of any School as explained further in the Schools and School members tables).

STUDENTS

+---------------+---------------+---------------+
| Student ID    | Admin ID      | Student name  |
+---------------+---------------+---------------+
| 1             | 1             | Mike          |
| 2             | 1             | Edward        |
| 3             | 1             | John          |
| 4             | 1             | George        |
+---------------|---------------+---------------+

The Administrators table contains the people who will use the script and oversee the Students/Schools table

ADMINISTRATORS

+---------------+---------------+
| Admin ID      | Admin name    | 
+---------------+---------------+
| 1             | Mr James      | 
| 2             | Mr Davies     | 
| 3             | Ms Lewis      | 
| 4             | Mrs Walsh     | 
+---------------|---------------+

Just from the example above, you can see that all current students have been registered by Mr James.

 

The Schools table, similar to the Students table, is simply a list of Schools registered by an administrator (containing an 'admin_id' related field).

SCHOOLS

+---------------+---------------+---------------+
| School ID     | Admin ID      | School name   |
+---------------+---------------+---------------+
| 1             | 1             | Hilbre        |
| 2             | 1             | Ridgeway      |
| 3             | 2             | Grammar       |
| 4             | 2             | Calday        |
+---------------|---------------+---------------+

From the above example, Administrator Mr James has registered Schools Hibre and Ridgeway, whilst Mr Davies has registered schools Grammar and Calday.

 

Now, for the complicated part that I'm struggling with. Administrators and Students should be interchangeable. For example, Administrator Mr James may 'belong' to any School created by Mr Davies - as may any of his students. I've tried to keep track of Administrators/Students/Schools by using a School members table.

SCHOOL MEMBERS

+---------------+---------------+---------------+
| *Member type  | Member ID     | School ID     |
+---------------+---------------+---------------+
| 1             | 1             | 1             |
| 1             | 2             | 2             |
| 1             | 1             | 2             |
| 0             | 2             | 2             |
+---------------|---------------+---------------+

* 0 = Student
  1 = Administrator

Without trying to complicate matters, the table above shows that Mr James and one of his students are both members of a School registered by a different administrator.

 

The query I'm trying to write would be; If I'm logged in as Mr James (or any other administrator), I'd like to query these tables to get a list of students that I've created, students that belong to my schools AND students that belongs to other schools that I'm a member of. I hope that makes sense and I hope I haven't over complicated it - is there's any easier way to handle this data, I'm all ears!

 

EDIT:

 

In the above query, I'd also need a GROUP_CONCAT list of Schools that the student belongs to (if they belong to any) if possible!

 

Thanks for any help

Edited January 3, 2016 by GuitarGod

[benanamen]

Lets start with the DB design. What are Students and what are Administrators? They are People. Depending on exactly what your doing and what you may do in the future, you could have problems splitting 'People' into two tables which it seems you are already at.

 

Students and Administrators are roles the people play. It is very possible for an Administrator to be a Student and a Student to be an Administrator. The result of that would be duplicating data which violates Database Normalization.

 

What you would want to do is have a 'persons' table with all the people in it, a role table with Administrator and Student as role options,  person_role table with the person.person_id and role.role_id. This will allow for unlimited persons with unlimited roles to grow infinitely just by adding a new person and a new role.

 

Get to this point and we can go from there.

Edited January 3, 2016 by benanamen

[Barand]

I agree with Benanamen. The model I used assumes a member can have only one role (Admin or Student) and is attached. The query to get your list would be a union of the three types of selection that you want.

--
-- select students created by Admin #10
--
SELECT member_name
FROM members
WHERE created_by = 10
AND role_id = 2
UNION
--
-- select students from schools
-- created by admin #10
--
SELECT member_name
FROM members m 
INNER JOIN school_members sm USING (member_id)
INNER JOIN schools s USING (school_id)
WHERE s.created_by=10
AND m.role_id = 2
UNION
--
-- select students from schools
-- where admin #10 is a member
--
SELECT member_name
FROM members m 
INNER JOIN school_members sm1 ON m.member_id = sm1.member_id
INNER JOIN school_members sm2 ON sm1.school_id = sm1.school_id
WHERE sm2.member_id = 10
AND m.role_id = 2
;

[GuitarGod]

Thank you both sincerely! I've taken your advice Benanamen and changed the tables accordingly.

 

And Barand, as ever, your code is spot on

 

I've been trying to add a GROUP_CONCAT to the query so that if a student belongs to any schools, the school ID and name are returned. I'm not having much luck but I'll keep trying. Out of interest, is there an easy way to achieve this?

 

Thanks once again for all your advice/help!

[Barand]

Yes.

SELECT member_name
, studs.member_id
, GROUP_CONCAT(school_name, '(', s.school_id, ')' SEPARATOR ', ') as schools
FROM
	(
	SELECT member_name
	, member_id
	FROM members
	WHERE created_by = 10
	AND role_id = 2
	UNION
	--
	-- select students from schools
	-- created by admin #10
	--
	SELECT member_name
	, m.member_id
	FROM members m 
	INNER JOIN school_members sm USING (member_id)
	INNER JOIN schools s USING (school_id)
	WHERE s.created_by=10
	AND m.role_id = 2
	UNION
	--
	-- select students from schools
	-- where admin #10 is a member
	--
	SELECT member_name
	, m.member_id
	FROM members m 
	INNER JOIN school_members sm1 ON m.member_id = sm1.member_id
	INNER JOIN school_members sm2 ON sm1.school_id = sm1.school_id
	WHERE sm2.member_id = 10
	AND m.role_id = 2
	) studs
LEFT JOIN school_members USING (member_id)
LEFT JOIN schools s USING (school_id)
GROUP BY member_name
;