Modeling a few SQL table relationships with NetflixGraph

[Mark Petrovic]

Hello.

I'm experimenting with NetflixGraph, and am wondering if someone could get me started with how to model the relationship between two SQL tables.  For example,

users.prof_id refers to profile.id

alias.uid refers to users.id

I think I can put NFG to good use, but I'm having a bit of trouble getting started.

Thanks much.

[dkosz...@netflix.com]

Hi Mark,

I'm making some assumptions here, so please correct me if I get anything wrong:

It sounds like you'll need two types of nodes, "users" and "profiles".

First, you'll need to gather all of the identifiers for your users and profiles, and map them to ordinals.  As an example, let's say you have a set of user ids and profile ids, both represented as String objects, and you're pulling them from ResultSets.  You'll use one OrdinalMap<String> per type:

OrdinalMap<String> usersOrdinalMap = new OrdinalMap<String>();
OrdinalMap<String> profilesOrdinalMap = new OrdinalMap<String>();

while(userResultSet.next()) {
usersOrdinalMap.add(userResultSet.getString("id"));
}
while(profileResultSet.next()) {
profilesOrdinalMap.add(profileResultSet.getString("id"))
}

Now you can create an NFBuildGraph which will contain the mapping between your users and profiles:

NFBuildGraph buildGraph = new NFBuildGraph(
new NFGraphSpec(
new NFNodeSpec(
"user"
new NFPropertySpec("hasProfile", "profile", NFPropertySpec.SINGLE)
),
new NFNodeSpec("profile")
)
);

Now you're ready to populate the build graph with connections.  Note that I made the assumption that you are mapping one profile to one user.  If this is not the case, you'll want to use NFPropertySpec.MULTIPLE instead of NFPropertySpec.SINGLE as above.  You probably have a mapping between your users and profiles persisted somewhere.  Let's say you're retrieving them from a join table with another ResultSet:

 

while(joinResultSet.next()) {
String userId = joinResultSet.get("user_id");
String profileId = joinResultSet.get("profile_id");

int userOrdinal = usersOrdinalMap.get(userId);
int profileOrdinal = profilesOrdinalMap.get(profileId);

buildGraph.addConnection("user", userOrdinal, "hasProfile", profileOrdinal);
}

Now your build graph has all of the connections for this mapping.  You can compress it and serialize it as detailed in the Usage section of the wiki.