I am designing a schema for my new application. During this process, I am stuck at one point. Here is my use case.
I have three entities, User, Group, and Company.
They are related to each other as many to many. For example,
User can be a part of multiple companies,
A company can have multiple users.
User + Company can have multiple groups. (User + Company + Group combination should be unique)
Following is SQL schema which represents this relationship
Table: User
UserId Name
1 Alice
2 John
Table: Group
GroupId Name
11 Group 1
12 Group 2
Table: UserGroupMap
Id UserId CompanyCode GroupId
1 1 1234 11
2 1 1234 12
3 1 5678 11
4 2 1234 12
Here is my attempt to convert this use case into DGraph schema
type User {
name
companyCode
userGroups
}
type Group {
name
}
name: string @index(hash) .
companyCode: string @index(hash) .
userGroups: [uid] @reverse .
But with this approach I can able to maintain uniqueness of User + Company + Groups, but now I am duplicating user information for there each company.
How can I transform this into DGraph schema?
Any thoughts?
Check the UserGroupMap table in SQL schema. For UserId 1 (Alice) we have 2 companies associated (1234 and 5678). If we migrate this data into DGraph schema then we will have 2 user nodes as follows.
Yes, they are separate concepts/entities. They can be related to each other through User only.
But we have one specific use case.
User + Company + Group combination should be unique.
type User {
name
groups
companies
}
type Company {
code
}
type Group {
name
}
name: string @index(hash) .
code: string .
groups: [uid] @reverse .
companies: [uid] @reverse .
This will return all the companies and groups associated with the user. But How can I get a list of groups associated with the User + Company combination?
e.g. Refer to SQL data from UserGroupMap
If I pass User = Alice and Company = 1234 it should return Groups = [Group1, Group 2]
If I pass User = Alice and Company = 5678 it should return Groups = [Group 1]
Dgraph does not currently support the concept of unique across multiple types. There are a few different ways to accomplish the use case:
get the list of groups for User + Company combinations
Option 1, with a map type:
This would create a new type to handle this relationship as a node. An edge can only link between two nodes, so when a link is needed between more than two nodes then a type is needed.
type UserGroupMap {
user: User
company: Company
group: Group
}
Then you can do a query with cascade to get the group of any specific user/company combination
query ($user: uid! $company: uid!) {
groups(func: (type(UserGroupMap))) @cascade {
user @filter(eq(uid,$user))
company @filter(eq(code,$company))
group {
uid
}
}
}
Option 2, with edges between group to companies and users.
Thinking about the use case leads me to believe that the Groups are related to the Users and Companies. This would enable a form of access control of the group depending on the User and the Company.
type Group {
users: [User]
companies: [Company]
}
In this schema you would not need to query the map, but rather just the Group itself.
I think, Option 1 is the perfect fit for my use case, because in Option 2 we still not able to maintain any uniqueness/ relation between User + Company + Group.
@sandeepkangude, can I ask to clarify your use case. Can you clarify this at all with a real world example and use case? Are you trying to implement a company/user/group access controls? I think there still might be a better schema design to handle your needs, but I don’t understand the use case at the moment.
But to answer your question specifically… While the use of cascade directive does result in the correct results, it is not optimized. The main reason is that it needs to read every node in the UserGroupMap, traverse all of their edges to the user and company and group. Then after it gets all of the results the cascade filters the data to only those that have all fields. Essentially the query is trying to traverse a ton of edges to conclude that it doesn’t need that data. Maybe a reversed query might help. Dgraph doesn’t do any kind of query optimization per se and it leaves that on the user to form the best queries knowing their own data. With DQL, maybe you can do a multiple block query that would get the results better:
query ($user: uid!, $company: uid!) {
var(func: eq(uid, $user)) {
ids1 as ~user { # reverse the edge
uid
}
}
var(func: eq(uid, $company)) {
ids2 as ~company { #reverse the edge
uid
}
}
groups(func: uid(ids1)) @filter(uids(ids2) AND type(UserGroupMap)) {
uid
}
}
This is my attempt to optimize without knowing more about your use case. @MichelDiz can you check me on my DQL syntax here? I am still not confident with DQL syntax and I don’t have a dev server running to test code on.
Yes, it is a valid use case in my project. Let me provide some data to support this use case. Note: Here Company means an entity of a company.
Example: Facebook has 2 offices one in SFO and one in New York. Those two offices are entities. User Alex is working for Facebook and he is having different roles for each entity.
Alex Facebook-SFO Quality
Alex Facebook-SFO Finance
Alex Facebook-NY Quality
Alex Facebook-NY Marketing
We need to maintain User + Entity + Group as a unique combination.
I hope I am able to explain the problem statement correctly.