Graph Database Planning

[MatthewVita]

Bethany, a new Entelekia volunteer that studies MIS with a focus on databases, would like to get more experience with databases and learn NoSQL (Neo4j, our targeted database, is 1 of the 4 types of NoSQL databases).

I figured it would be best to write up the explore feature's database in "regular" relational SQL so she can understand the goals for building up our Neo4j graph database (she has experience with Oracle relational SQL)

tables:

CREATE TABLE [opportunities] (
[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[title] VARCHAR(127)  NOT NULL,
[desc] TEXT  NOT NULL,
[url] VARCHAR(127)  NULL,
[author] VARCHAR(127)  NOT NULL,
[addedBy] VARCHAR(127)  NOT NULL,
[addedByUrl] VARCHAR(127)  NULL,
[time] VARCHAR(127)  NOT NULL,
[isPrivate] BOOLEAN  NULL,
[isChildFriendly] BOOLEAN  NULL,
[isNonFree] BOOLEAN  NULL,
[type] VARCHAR(127)  NOT NULL
);

CREATE TABLE [opportunities_links] (
[topicKey] INTEGER  NULL,
[opportunitiesKeys] INTEGER  NULL
);

CREATE TABLE [resources] (
[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[title] VARCHAR(127)  NOT NULL,
[desc] TEXT  NOT NULL,
[url] VARCHAR(127)  NULL,
[author] VARCHAR(127)  NOT NULL,
[addedBy] VARCHAR(127)  NOT NULL,
[addedByUrl] VARCHAR(127)  NULL,
[time] VARCHAR(127)  NOT NULL,
[isPrivate] BOOLEAN  NULL,
[isChildFriendly] BOOLEAN  NULL,
[isNonFree] BOOLEAN  NULL,
[type] VARCHAR(127)  NOT NULL
);

CREATE TABLE [resources_links] (
[topicKey] INTEGER  NOT NULL,
[resourcesKeys] INTEGER  NOT NULL
);

CREATE TABLE [topics] (
[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
[title] VARCHAR(127)  NOT NULL,
[course] BOOLEAN DEFAULT 'false' NOT NULL
);

CREATE TABLE [topics_links] (
[topicKey] INTEGER  NOT NULL,
[topicKeys] INTEGER  NOT NULL
);

...to get related topics:

SELECT
  id,
  title,
  course
FROM
  topics top
JOIN
  topics_links junct
ON
  top.id = junct.topicKeys
WHERE
  junct.topicKey = ?

...to get topics mapped to opportunities:

SELECT
  *
FROM
  opportunities opps
JOIN
  opportunities_links junct
ON
  opps.id = junct.opportunitiesKeys
WHERE
  junct.topicKey = ?

...to get topics mapped to resources:

SELECT
  *
FROM
  resources res
JOIN
  resources_links junct
ON
  res.id = junct.resourcesKeys
WHERE
  junct.topicKey = ?

[MatthewVita]

...now that we're on the same page... how do we want to lay out our graph db?