How to efficiently design database of multi list application

Question

I apologize for the verbosity but I'm trying to reach a design for a sample application I am working on. Below I've explained an Example case, Desired Queries for the example case, and my DB design

I am looking for suggestions on how to improve my current design that'll enable me to answer the queries I have in the example section.

Example:

Scenario:

• John creates two topics Math, and Science
  • Adds items calculus & algebra to Math, and Physics to Science
• James creates two topics Math, and Music
  • Adds items pre-calc to Math, and Rock to Music
• Lisa creates two topics Math, and CompSci
  • Adds items Linear Algebra to Math, and Java to CompSci
• John shares his item calculus from topic Math with James
  • James accepts the item
  • James shares the same item with Lisa
  • Lisa accepts the item

Desired Queries:

John

• List of topics and count of items in each will show:
  • Math - 2, Science - 1
• List of items in Math will show each items' share count and who shared it
  • Calculus - 2 - you, Algebra - 0 - NULL

James

• List of topics and count of items in each will show:
  • Math - 2, Music - 1
• List of items in Math will show each items' share count and who shared it
  • Calculus - 2 - James, Pre-Calc - 0 - NULL

Lisa

• List of topics and count of items in each will show:
  • Math - 2, CompSci - 1
• List of items in Math will show each items' share count and who shared it
  • Calculus - 2 - James, Linear Algebra - 0 - NULL

Current DB Design

User:

ID     username
----   ----------
1        John
2        James
3        Lisa

Topics

ID      Topic_Name      User_id
---     ---------       --------
1        Math           1
2        Science        1
3        Math           2
4        Music          2
5        Math           3
6        CompSci        3

Items

ID     Item_Name        Topic_Id
---   ----------       ----------
1      Calculus           1
2      Algebra            1
3      Physics            2
4      Pre-Calc           3
5      Rock               4
6      Linear Algebra     5
7      Java               6

Share

ID   Item_Id    Sent_user_id    Accepted_user_id
---  -------   -------------    -----------------
1     1          1                 2
1     1          1                 3

To me the above DB design makes sense but I'm having trouble getting the query results I want. I'm not sure whether my queries can be improved or I should change my design to be better suited for my desired queries

Query 1: Get Topics and their item count BY USERID

-- This only works for certain cases
SELECT t.topic_name, count(topic_name) as item_count
FROM Topics t
INNER JOIN Items i on i.topic_id = t.id
INNER JOIN User on u.id = t.user_id
INNER JOIN Share s on s.item_id = i.id
WHERE u.id = 1
GROUP BY t.topic_name

The above query returns following for John

topic_name      item_count
-----------    ------------
Math              3
Science           1

But it should return the following for John

topic_name      item_count
-----------    ------------
Math              2
Science           1

should return following for James

topic_name      item_count
-----------    ------------
Math              2
Music             1

should return following for Lisa

topic_name      item_count
-----------    ------------
Math                2
CompSci             1

Note that item_count should be 2 for James. I believe the above query works fine for other users.

Query 2: Get items and their shared count and who originally shared them BY USERID

   --I'm not sure how to start on this. I've tried union with Items table and Share table
   -- but that also works only for few users and not for all cases. 

for John for Math I would expect:

Item Name    Share Count     Orig Shared By
---------    -----------    -----------------
Calculus       2              You
Algebra        0              NULL

For James for Math:

Item Name    Share Count     Orig Shared By
---------    -----------    -----------------
Calculus        2              James
Pre-Calc        0              NULL

For Lisa for Math:

Item Name         Share Count     Orig Shared By
---------         -----------    -----------------
Calculus              2              James
Linear Algebra        0              NULL

Update

Based on the comments and answer I've changed the DB Schema a bit. I've made a separate join table which establishes the relationship between Users-Topics-Items.

Please see this sql fiddle: http://sqlfiddle.com/#!4/15211/15

But I still need help with the queries. I feel like I'm close.

Answer 1

With new design as:

Query 1: Get Topics and their item count BY USERID

SELECT DISTINCT u.username, T.Topic_Name ,   
(
  (SELECT count(*) FROM UserTopicItem
   WHERE UserTopicItem.topic_id= t.id
   AND   UserTopicItem.user_id = UTI.user_id)
  +
  (SELECT count(*) FROM Shares
   WHERE Shares.Topic_id = UTI.Topic_id
   AND Shares.Accepted_user_id = u.id)
) AS item_count
FROM UserTopicItem UTI
INNER JOIN USERS u ON u.id = UTI.user_id
INNER JOIN topics T ON T.id = UTI.Topic_Id
-- where u.id = ?
ORDER BY u.username;

Query 2: Get items and their shared count and who originally shared them BY USERID

SELECT i.item_name, t.topic_name
,(SELECT count(*) FROM shares 
  WHERE shares.Item_Id = i.id
  and shares.user_id   = u.id
  and shares.topic_id  = t.id
 ) AS shared_count
,decode((SELECT count(*) FROM shares 
  WHERE shares.Item_Id = i.id
  and shares.user_id   = u.id
  and shares.topic_id  = t.id
 ),0,'Null',u.username) AS Orig_Shared_By 
FROM UserTopicItem UTI 
INNER JOIN Items i  ON UTI.item_id = i.id
INNER JOIN topics t ON UTI.topic_id = t.id
INNER JOIN Users u  ON UTI.user_id = u.id
-- where u.id = ?
order by u.id,shared_count desc;

sqlfiddle demo

Answer 2

I think your design should be normalized a bit more:

Users:

Id     username
----   ----------
1        John
2        James
3        Lisa

Topics:

Id      Topic_Name
---     ---------
1        Math
2        Science
3        Music
4        CompSci

SubTopics:

Id    SubTopic_Name    Topic_Id
---   ----------       --------
1      Calculus           1
2      Algebra            1
3      Physics            2
4      Pre-Calc           1
5      Rock               3
6      Linear Algebra     1
7      Java               4

That way, you start with users and Topics/SubTopics being orthogonal sets — completely unrelated, if you prefer. From there, then toss in new fields and/or relationships to connect them per your use-case.

You seem to need to track permissions. There are many ways to store permission-related data. For inspiration, look into ACL (access control lists) and RBAC (role-based access control) in particular. (If you're using PHP, look into the Symfony or Yii frameworks for implementation ideas.)

Speaking personally, I'd advise to track ownership using an entirely separate table, rather than using an owner_id in SubTopics: you never know when you might want shared ownership of a node in the future.

For the sake of simplifying your example, though, we'll add an owner_id field regardless, and stick to a simplistic permissions table:

SubTopics becomes:

Id    SubTopic_Name    Topic_Id    Owner_Id
---   -------------    --------    --------
1      Calculus           1           1
2      Algebra            1           1
3      Physics            2           1
4      Pre-Calc           1           2
5      Rock               3           2
6      Linear Algebra     1           3
7      Java               4           3

SubTopic_Access:

User_Id  SubTopic_Id
--—----  --—------—-
   1          1            # John created Calculus
   1          2     
   1          3     
   2          4            # James created Pre-Calc
   2          5     
   3          6            # Lisa created Linear Algebra
   3          7     
   2          1            # James accepted Calculus
   3          1            # Lisa accepted Calculus

(The benefit of tossing all users that have access, including owners, in the latter table is to simplify queries down the road.)

If you need to track invites outside of emails, either toss the invites in a different "Invites" table, or add a field in the above table if you don't mind carting Is_Active = 1 clauses all over the place. (Fwiw, the latter is tedious unless you're using views, and not so great for indexes.)

Your first query seems to be counting the number of subtopics a user has access to in each topic. A starting point could be this:

select s.Topic_Id, count(s.Id)
from SubTopics s
join SubTopic_Access a
  on a.SubTopic_Id = s.Id
where a.User_id = ?
group by s.Topic_Id

Your second query seems to be counting the number of other users who can access a subtopic a user has access to. (I likewise imagine that Orig Shared By should actually display John each time.) A starting point count be this:

select s.Id, count(o.User_Id), s.Owner_id
from SubTopics s
join SubTopic_Access a
  on a.SubTopic_Id = s.ID
left join SubTopic_Access o
  on o.SubTopic_Id = s.ID
 and o.User_id <> a.User_Id
where s.Topic_Id = ?
  and a.User_id = ?
group by s.Id, s.Owner_id

(In the above, the Owner_Id field will always be populated. You could use NullIf on it to nullify the value where needed if needed.)