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.