Help converting relational database schema to graph

[Phil]

Hi, I'm just starting out investigating graph databases and OrientDB and I'm trying to work out how to best model this scenario:

In my app a user can tag a resource but I also need to know who tagged which resource. In a relational database I would simply have a join table which can be used to discover which user applied a particular tag. In my initial attempt at converting this to a graph model I had 3 vertices for Person, Resource and Tag but couldn't work out how to define edges that allow me to determine which person applied a tag.

All I could come up with is to add a fourth vertex to represent the actual tagging event ('Review' in the schema below).

Is this how you experts would handle such a scenario or is there a better way?

[Colin]

Hi Phil,

Two quick questions:

1) Can a Resource have multiple tags applied to it?

2) You want to be able to determine who created a specific tag, right?

If my assumption is correct based on looking at your relational schema, I would create an edge between the Person and the Tag directly.

-Colin

Orient Technologies

The Company behind OrientDB

[Phil]

Hi Colin, thanks for your reply.

1) Yes a Resource can have multiple tags

2) I want to determine who applied a specific tag to a specific resource (could be more than one person). 

Multiple users may apply the same tag to the same resource, which is where I think your suggestion may not work to add a Person-Tag edge.

With the schema below I could work out:

* Who tagged a particular resource (any tag)

* Which tags are applied to a particular resource

* Which tags a person has used to tag resources

But, as far as I can tell, not which tags a specific person has applied to a specific resource, or which people tagged a specific resource with a specific tag.

-Phil

[Patrick Hoeffel]

Phil,

What if you made the Tag itself the Edge class, and perhaps add a couple of properties to the edge to give it appropriate context? Then your three-node graph above becomes a two-node graph with multiple "Tag" edges between them? You could add the timestamp to record when the Person add the Tag to the Resource.

Would that work?

Patrick

[Colin]

Hi Phil,

Another quick question  and then I'll respond to your last post based on your answer.  Will there be a single instance of each tag (say "Family Photo") that multiple Person instances and Resources will use, or will there be multiple tag instances with the same name and properties just linked to different Persons and Resources?

[Phil]

I was planning to have only one instance of each tag; this seems more 'normalised' to me and may make certian queries easier/more efficient e.g. discover all the unique tags for a resource without having to group them, or find all resourses with a particular tag.
It's not set in stone though and I'm open to suggestions.

[Phil]

Thanks Patrick, that could work. The only thing I would be concerned about is the implications for querying the data, it would mean multiple instances of the Tag edge for the same tag content which would probably result in more complex and possibly less efficient queries for anything relating to unique tags. It's an interesting idea though that I will consider and it keeps the schema nice and simple.
-Phil

[Colin]

Hi Phil,

You can accomplish everything you want with the three node model.

With some of the more specific queries you will need to use nested select statements to filter out specific tags or persons (or even resources if you wanted).

Let me know if you need further detailed help.

Best regards,

-Colin

Orient Technologies

The Company behind OrientDB

[Phil]

Hi Colin,

Maybe I'm missing something obvious but I still can't envisage how the 3 node model will work.

In the example data below I can determine that both Bob and Alice have tagged Resource1 and that Resource1 has been tagged 'red' and 'blue'. I can also determine that Bob has applied the 'red' tag to a resource but how do I tell if he applied this to Resource1 ?

If it's a question of my edge definitions I'd be grateful if you could provide some specific examples that would allow the 3 node model to fit my requirements.

Thanks,

Phil

[a...@yakyn.com]

Phil, I think it is easy to accomplish by adding property on the edge.

Let us say you have Person --->tagged---> Resource, where "tagged" is edge. You could add "tag_type" property (link to tag vertex) on this edge class.

Alternatively, if finding what resources are tagged with which particular tag is more important than who tagged - you could make edge between tags and resources, but then have "person_who_tagged" link property (to Persons class) on this edge class, though.

Hope this kind of semi hyper-graph is not deprecated in ODB.

Ata

[Colin]

Hi Phil,

I may just be missing something, but here's a simple example of what I'm thinking.

select from (select expand(in("AppliedTag")) from (select expand(out("HasTag")) from Resource where Id="Resource1") where Id="Red") where Name = "Bob"

This will return Bob after selecting Resource1 and then selecting its associated tags, filtering on the Red Id.

Does that make sense?

-Colin

Orient Technologies

The Company behind OrientDB

[Phil]

With the data in my diagram would this not also return Bob if he applied the red tag only to Resource2 and not Resource1 ?

He tagged Resource1 only with blue.

[Phil]

Thanks Ata, yes that would work if it's possible to add a link property to an edge.

[Colin]

No, because the inner most select specifies "Resource1" as a starting point.

-Colin

Orient Technologies

The Company behind OrientDB

[Phil]

Hi Colin,

I've put together a quick test of this and it does indeed return Bob even if he did not apply the Red tag to Resource1.

Attached are 2 files: 

1) I run test_db_schema.sql as an argument to the console.sh script to create the database and schema.

2) I then start the console, connect to the database then copy and paste the contents of test_db_data.sql into the console after issuing the 'script sql' command. I don't know why but I couldn't use the let statement from within a file.

I have included your query at the end of the script and the Bob record is returned even if he didn't tag Resource1 with Red.

-Phill

[Ata Annamamedov]

I'm glad it could work for you. 

And yes, it is absolutely possible to add link property on the edge (we do that without any problem).

With best regards,

Ata

--

---
You received this message because you are subscribed to a topic in the Google Groups "OrientDB" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/orient-database/EHIGVNw_hUk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to orient-databa...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Colin]

Hi Phil,

It's the query that never ends...!

I looked over your schema and data, and maybe I misunderstood you or came up with my own interpretation, but in my mind each tag was its own instance 'owned' by a particular person.

I would expect that a person could create his own custom tags from maybe common derived types that could be used for general queries about tags or each tag could have some kind of type identifier.

This solves all your stated queries and avoids having to have links on the tagged and taggedwith edges themselves (which will prevent them from being lightweight edges).

In your relational model, the PersonResourceTag serves as the 'instance' data.

I'm curious what you decide to do.

Good luck (and let me know)!

-Colin

Orient Technologies

The Company behind OrientDB