Discussion Forum

What is the schema for this?

What is the schema for this? It is written in C++ for an SQL database.

add_structure(“CREATE TABLE smem_uncommitted_spread (lti_id INTEGER, num_appearances_i_j REAL,num_appearances REAL, lti_source INTEGER, sign INTEGER, PRIMARY KEY (lti_source, lti_id)) WITHOUT ROWID”);

To be honest, it’s very hard to tell! It looks like a co-occurrence matrix of some kind?

I understand it would probably be a large undertaking (involving talking to all the people with a bit of knowlege and reading the existing endpoints that query the database), but I think you’ll struggle the least if you took the time to try to map out the entire model and domain with new, meaningful names and the inter-relationships between them. I imagine it might take a few weeks to map out and organise a good schema but I think the payoff would be worthwhile!

That being said, if you just want to proceed naiively you can try to just insert an entity type smem_uncommitted_spread with the attributes lti_id, etc.

However, let’s say num_appearances_i_j is encoding a matrix where the value at index [i,j] represents a number attached to an edge/the number of edge between some data point i and j, then you would actually want to move to a real graph structure instead of a matrix, creating a relation between concept i and j, with an attribute representing the number of occurrences. Which takes me back to actually trying to understand what the data represents and re-modeling it properly!

This might be a lot to digest, and I probably got a lot of guesses wrong, but I hope it helps a bit!

What got me confused was the existence of 2 keys and the command WITHOUT ROWID.
Is this a valid schema?

smem_uncommitted_spread sub entity,
key lti_id,
key lti_source,
owns num_appearances_i_j,
owns num_appearances,
owns sign,
lti_id sub attribute, value long,
lti_source sub attribute, value long,
num_appearances_i_j sub attribute, value double,
num_appearances sub attribute, value double;

yes, minus syntax errors. I recommend going through the examples in the docs to get to grips with it:

smem_uncommitted_spread sub entity,
owns lti_id @key,
owns lti_source @key,
owns num_appearances_i_j,
owns num_appearances,
owns sign;
lti_id sub attribute, value long;
lti_source sub attribute, value long;
num_appearances_i_j sub attribute, value double,
num_appearances sub attribute, value double;