THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

What’s in a name? How should SQL Server 2017 Graph Edge tables be named?

Dennes Torres recently wrote a really excellent article introducing Graph Objects in SQL Server 2017. You’ll find it here: https://www.simple-talk.com/sql/t-sql-programming/sql-graph-objects-sql-server-2017-good-bad/

I’ve also recently published a SQL Down Under podcast with Shreya Verma from the SQL Server team, where we discussed Graph extensions to SQL Server. (It’s part of a joint interview. We also discussed Adaptive Query Plans with Joe Sack). You’ll find that here: http://sqldownunder.com/Podcasts

I loved Dennes’ article but one aspect that I want to talk a little more about is the naming of Edge tables. As I read the article, I was a little troubled about the edge naming. (Let me stress that it’s a very, very minor item in a very good article and just my opinion).

And then I got thinking about other aspects of the naming. I think it’s an interesting area because we haven’t had these tables as formal parts of SQL Server before. I’m probably a bit anal about naming but I really think it matters. Here’s the issue:

Dennes had Node tables for ForumMembers, and ForumPosts. All agreed there. And I like them both being plural. I think that’s appropriate here. Generally I like tables to be plural as sets of data, apart from when the table can only ever contain a single row. I don’t see the Node tables as any exception to this.

Depending upon the other tables, I might have renamed them as Forum.Members and Forum.Posts (rather than dbo.ForumMembers and dbo.ForumPosts) but that would require knowledge of what other tables there are.

But then the edges are defined like this:

Written_By:

$from_id will be the post

$to_id will be the member

Likes:

$from_id will be who likes

$to_id will be who/what is liked

Reply_To:

$from_id will be the main post

$to_id will be the reply to the main post

Implied Direction

The first aspect to consider is the implied direction of each of these. Based on these definitions, I think it’s useful to write them as sentences to see that the flow works like this:

($from_id) the post Written_By the member ($to_id) -- > agreed

($from_id) who Likes who/what ($to_id) -- > agreed

($from_id) the main post Reply_To the reply to the main post ($to_id) -- > doesn’t work for me

Note that Reply_To doesn’t work in the direction from $from_id to $to_id. I think that should have been the other way around.

Positive (Forward) Direction

I’d also like to see the tables use a forward direction naming rather than reverse (like “Written By”). So perhaps:

($from_id) the member Wrote the post ($to_id)

($from_id) who Likes who/what ($to_id)

($from_id) the reply to the main post RepliesTo the main post ($to_id)

Tense

At this point, notice that the tense is now different. Wrote is past tense, Likes is present tense, as is RepliesTo. I started wondering about whether this is ok because the article was written in the past, but I started wondering about Likes. Does the like still apply? We actually don’t know. We know that he/she did like it at the time but we have no current knowledge. They may have changed their mind. So, aligning the tense with our knowledge, perhaps we should have:

($from_id) the member Wrote the post ($to_id)

($from_id) who likes Liked who/what is liked ($to_id)

($from_id) the reply to the main post RepliedTo the main post ($to_id)

Composite Names

Finally, I’m ok with composite names like RepliedTo when needed but I was left wondering if there was a simpler, more direct way to say the same thing ie: a single word as a verb. The only one that I can think of that might work is this:

($from_id) the member Wrote the post ($to_id)

($from_id) who likes Liked who/what is liked ($to_id)

($from_id) the reply to the main post Answered the main post ($to_id)

But I’m not sure about this one, as Answered tends to imply a solution, where RepliedTo doesn’t.

I’d love to hear your thoughts.

Published Monday, July 10, 2017 12:19 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement