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:
Desired Queries:
John
James
Lisa
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.
For an application and its database to be truly multi-lingual, all texts should have a translation in each supported language – not just the text data in a particular table. This is achieved with a translation subschema where all data with textual content that can reach the user's eyes is stored.
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
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.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With