Hi and Part 1 of a public discussion/request for help

[jeremyosborne]

Hi guys.

I've stroked your egos a lot personally, but let me just do it
publicly: your database lets me do what I'm best at (code JavaScript)
and ignore the things I'm not the best at (control and manage
databases).

Yet as I spoke to Brent about yesterday, I realized posting this up
for discussion, given I'm not a data or database guru, might speed up
my process of sorting things out. It's a multi-part post where I'll
try to make each problem I'm facing discreet.

Background

I'm making a multi-language flashcard system. I'm not recreating
wiktionary. I'm not trying to do what language learning systems do.
Personally, I want a system that I can use to build my vocabulary
(always my weakness) across multiple languages. My first focus is
Engish-German-Spanish, but eventually it would be fun to open up the
matrix of language mappings to all of the languages found in the ISO
693-2.

Before I knew about you guys, I was going to store everything in XML,
described by the XSD located at http://jeremyosborne.com/ns/phraseMatrix/phraseMatrix.xsd.
You can probably imagine the giant, single table mindset I'm in if you
have a look at that.

The communication to the NextDB backend from my flashcards is insanely
simple and as I want to expand the app to be something useful, I ran
into the following. Given what I said above, I have been working with
a single table in NextDB. It's not something I would normally do, but
I thought a single table would help me simplify in this case.

Problem #1 - Searching dynamic columns with NextQuery

My first table looks like this (I'm going to post and pray the
formatting sticks):

id | eng | deu
1 | green | gruen

If the formatting doesn't stick, know that the top row are the column
names and pipes delimit.

Perfect! Two columns, nice and easy to manage. Now I want to expand to
the following:

id | eng | deu | spa
1 | green | gruen | verde

For reading, this is not a problem. I can use the simple mechanism of
retrieving a random card by id, which is how the flash cards work:
find the total number of rows in the data table; and grab a random
card from minId to maxId; retrieve from the data results object the
language that should display on the front of the card (eng in my case)
and the content that should display on the back of the card (which
could now be german or spanish).

As I move into finding a way for users to update data in the database,
I'm attempting to find the best method of querying columns dynamically
at runtime.

I believe this is where my big table approach will fail here.

In NextQuery I could not find a way to dynamically choose the column
name that I want to search for. If I constrain searching to 2 columns,
and my table has N columns, I believe I'm going to need to create
approximately N! queries in NextQuery if they look like the following:

NAME=get_phrase_eng_deu;
ROW phrase FROM PHRASEENTRIES;
WHERE (TEXT eng, TEXT deu){
phrase.eng = ${eng}
AND
phrase.deu = ${deu}
}

Would you gentlemen every be up to adding either a form of set
constraints or dynamic column selection to the variable?

One idea, using [] to denote an array of N number of items:

NAME=get_phrase_eng_deu;
ROW phrase FROM PHRASEENTRIES;
WHERE (TEXT phrase1, TEXT[] columns){
for ( i in ${columns} ) {
phrase.${columns}[i] = ${phrase1}
}
}

Another idea:

NAME=get_phrase_eng_deu;
ROW phrase FROM PHRASEENTRIES;
WHERE (TEXT phrase, TEXT column){
phrase.${column} = ${phrase}
}


Thanks for reading and for any thoughts and comments. I have another
related but different enough conundrum that I'll post in a separate
topic.

[geoff]

Hi Jeremy,

I'm pretty sure this is a classic data modeling problem. I like the
idea of dynamic column searching, and in fact I've been looking at
building a lucene index to do just that. However, I think what we are
looking at here is a problem that is typical in relational datamodels,
and the typical solution would be to use related tables. I'd do it
like this, conceptually (I'm going to use some SQL pseudocode code
here to illustrate, then we'll nextdb it):

CREATE TABLE NATIVE_WORD(PRIMARY KEY word_id, native_lang VARCHAR
(256), native_word VARCHAR(256))
CREATE TABLE TRANSLATE(FOREIGN KEY native ON NATIVE_WORD(word_id),
FOREIGN KEY translation ON NATIVE_WORD(word_id))

So we have a first table called NATIVE_WORD that stores every word and
it's native language code. example:

word_id | native_lang | native_word
1 EN dog
2 SP perro
3 EN cat
4 EN car
5 SP gato


And we have a second table that holds any translations you chose to
enter. Example:

native | translation
1 2
3 5

In SQL you can perform a translation 'cat' to 'gato' like this:

SELECT translated.native_word
FROM NATIVE_WORD as translate_me, NATIVE_WORD as translated, TRANSLATE
as translation_table
WHERE translation_table.native=translate_me.word_id AND
translation_table.translation=translated.word_id AND
translate_me.native_word='cat'

In NextQuery it works like this.

NAME=translate;
ROW native FROM WORDS;
ROW translated FROM WORDS;
ROW translation FROM TRANSLATIONS;
translation RELATED native VIA NATIVE;
translation RELATED translated VIA TRANSLATED;
WHERE(TEXT translateMe, TEXT targetLang){
native.word=${translateMe} AND translated.lang=${targetLang}
}
RETRIEVE translated;

Here is my data model:

http://www.nextdb.net/nextdb/service/geoff/flashcards/EXPORT

Click the link above and you'll see all my table, relationship, and
query definitions. You can import them into your own database through
the admin. Yeah, data model sharing!.

-geoff

On Oct 11, 7:43 am, jeremyosborne <jeremywosbo...@gmail.com> wrote:
> Hi guys.
>
> I've stroked your egos a lot personally, but let me just do it
> publicly: your database lets me do what I'm best at (code JavaScript)
> and ignore the things I'm not the best at (control and manage
> databases).
>
> Yet as I spoke to Brent about yesterday, I realized posting this up
> for discussion, given I'm not a data or database guru, might speed up
> my process of sorting things out. It's a multi-part post where I'll
> try to make each problem I'm facing discreet.
>
> Background
>
> I'm making a multi-language flashcard system. I'm not recreating
> wiktionary. I'm not trying to do what language learning systems do.
> Personally, I want a system that I can use to build my vocabulary
> (always my weakness) across multiple languages. My first focus is
> Engish-German-Spanish, but eventually it would be fun to open up the
> matrix of language mappings to all of the languages found in the ISO
> 693-2.
>
> Before I knew about you guys, I was going to store everything in XML,

> described by the XSD located athttp://jeremyosborne.com/ns/phraseMatrix/phraseMatrix.xsd.

[Eric Dorman]

What is this Next Query I am hearing about?

Sounds interesting.

-Eric

[geoff]

"NextQuery" is the name of our query language. It's kind of like SQL,
but "web safe" and without the need for primary keys and foreign keys.
This is possible because in NextDB supported the "Entity-Relationship"
paradigm directly through Relationships which you can define via the
admin. In fact, if you look in my rspnse to Jeremy's post, you can
contrast "SQL VS. NextQuery".

-geoff

[jeremyosborne]

Geoff,

Thank you for this, as it was much more help than I expected. I've
imported the tables and have been messing with the query.

Practically and conceptually I'm still a bit stuck on how to create
and manage relationships in NextDB. I'm a bit retro: in the past, when
I've been using Access or (much more rarely) MySQL I usually just
resort to one of the following methods:

employees
first | last | employer_id
walter | peck | 1

employer
id | name | state
1 | EPA | New York

or

employees
id | first | last
1 | walter | peck

employer
id | name | state
1 | EPA | New York

employees_to_employer
employee_id | employer_id
1 | 1

I'll admit, I'm doing all of my test table maintenance through your
quite nice admin pages and I've been practicing with the NextDB table
relationships that you pointed to in the last email. However, I can't
seem to get my relationships to work using my jeremy entered ids in my
id columns in my translations table.

Reading through the NextQuery guide, I found the reference to the
JSAPI Connnection.createRelationship method, and saw this signature:

createRelationship(<String> relationshipName, <String> rowId1,
<String> rowId2, <Function> callback)

This is nice, and please let me know if my following assumptions of
steps to take are correct:

1) Create my data tables.
2) Create a table specifically for relationships. In my case, my
TRANSLATIONS table mapping one to mane to my WORDS table.
3) Get the PKs of the words I wish to relate in my application.
4) Call createRelationship with the PKs provided to me from a previous
Query for a particular words.

Is there a way that I haven't figured out in the admin pages that is
the equivalent of the createRelationship method? Relating two tables
is easy, but I can't see how to form the relationships in the admin
pages.

[geoffrey hendrey]

From the programatic side, you are on the right track. You can get a row's SURID (encrypted PK) from either the insert callback from when you created the row, or from a query. And you're also right that createRelationship would be called twice, once for each word-row, to establish the row in the translations table. The admin doesn't have any way to do this. It's only available via JSAPI, and REST-RPC. It's just something we haven't got around to building a UI for. It's probably the next thing we should do.

If you want to create your own ID's and manage it all yourself, that's fine too, and nothing stops you from doing it. You could create a user-level column called "word_id" in the WORDS table, and create a first column in the TRANSLATIONS table called "native_word_id" and a second column called "translated_word_id". In that case your NextQuery expression would be:

NAME=translate;
ROW native FROM WORDS;
ROW translated FROM WORDS;
ROW translation FROM TRANSLATIONS;

WHERE(TEXT translateMe, TEXT targetLang){
  native.word=${translateMe} AND translated.lang=${targetLang}

  AND native.word_id = translation.native_word_id
  AND translated.word_id = translation.translated_word_id
}
RETRIEVE translated;

However, for any application that needs security, you have to be very careful with the "raw id" approach. Why? Since we have a javascript API, you'll be setting id's using the Connection.update(...) method in the JSAPI. For flashcards, no problem. For bank account, big problem, because the clear-text id's being used in relationships can be intercepted and changed. Boom, I just linked to someone else's bank account. That's the whole idea behind SURID ... it's to generate tamper-proof keys that encode information about where they came from, and what actions they can be used with. Massive overkill for flashcards, so just roll with the "manage your own IDs approach" for your app. Avoid any complexity you don't need.

-geoff

[jeremyosborne]

Thanks Geoff.

I'm going to implement using the SURIDs and do that from the client
side.

I appreciate your help on a Sunday :)

- Jeremy

> ...
>
> read more »

[Brent Hamby]

It is pretty straight forward to write scripts to populate your tables with related data using the JavaScript API  (or the Java API for that matter).  If you have data in XML, we could parse the XML and run the inserts with relationships.  You could also write a simple web interface for entering new words / translations into your tables.

There are two ways of establishing relationships with the JavaScript API.  You can relate two rows that are already in the tables using the Connection object directly, like this:

Connection.createRelationship(<String> relationshipName, <String> rowId1, <String> rowId2, <Function> callback)

Or  you can set the relationship at insert time, by setting the relationship between the new row you are inserting and an existing row in the database:

Insert.setRelationship(<String> rowId,<String> relationshipName)

Establishing the relationships at insert time is probably more straightforward and easy in most cases.
 
Thanks for the feedback,

Brent

--
Brent Hamby
brent...@gmail.com
mobile: 510.421.5232
---The content of this email is confidential.---

[geoffrey hendrey]

I think in jeremy's case, with the "association" table, he'll need to do at least one of the "createRelationship" variety.

-geoff

[geoffrey hendrey]

incidentally, I'd really like to put Jeremy's "flash cards" on the wiki as a sample application when it's ready.

[jeremyosborne]

But of course :)

I'm public domaining the code. Whenever I'm done, you can have at it.

On Oct 12, 6:57 am, geoffrey hendrey <geoff.hend...@gmail.com> wrote:
> incidentally, I'd really like to put Jeremy's "flash cards" on the wiki as a
> sample application when it's ready.
>
> On Mon, Oct 12, 2009 at 6:40 AM, geoffrey hendrey

> <geoff.hend...@gmail.com>wrote:

>
> > I think in jeremy's case, with the "association" table, he'll need to do at
> > least one of the "createRelationship" variety.
>
> > -geoff
>

> > On Sun, Oct 11, 2009 at 8:18 PM, Brent Hamby <brentha...@gmail.com> wrote:
>
> >> It is pretty straight forward to write scripts to populate your tables
> >> with related data using the JavaScript API  (or the Java API for that
> >> matter).  If you have data in XML, we could parse the XML and run the
> >> inserts with relationships.  You could also write a simple web interface for
> >> entering new words / translations into your tables.
>
> >> There are two ways of establishing relationships with the JavaScript API.
> >> You can relate two rows that are already in the tables using the Connection
> >> object directly, like this:

> >> *

> >> Connection.createRelationship(<String> relationshipName, <String> rowId1,

> >> <String> rowId2, <Function> callback) *

>
> >> Or  you can set the relationship at insert time, by setting the
> >> relationship between the new row you are inserting and an existing row in
> >> the database:
>

> >> *Insert.setRelationship(<String> rowId,<String> relationshipName)
> >> *

> >> Establishing the relationships at insert time is probably more
> >> straightforward and easy in most cases.
>
> >> Thanks for the feedback,
>
> >> Brent
>

> ...
>
> read more »

[jeremyosborne]

Just to follow up and close this particular conversation for me.

I finally got the hang of the NextDB relationships. Very easy and very
nice.

Thinking through what I want to do for a version 1, here's what I came
up with after our discussion:

1 Table (WORDS) consisting of ISO language of the word (column1) and
the word itself (column2). I'm no longer sure if I'm going to need my
own id column in the table anymore, I think I might need it to help me
randomize the content that gets requested.
1 Relationship named TRANSLATIONS: 1toMany from table WORDS to table
WORDS (self related)

As words are inserted into the table, create the relationships using
the the Connection.CreateRelationship method. Piece of cake.

Thanks for the help guys.

> ...
>
> read more »

[geoffrey hendrey]

Sounds great! The only drawback to this model is that each word-row is only allowed to be a translation from just one language (since each row is on the 'one' side of a 'one-to-many'.

However, at the cost of a little denormalization, you can just insert 'cat' twice and have one cat-row's translation point to 'gato' and another cat-row's translation point to to 'neco'.

A little denormalization goes a long way :-)

-geoff

[jeremyosborne]

Thanks for the pointer Geoff. In my tests I just assumed, given I was
only doing one to one relationships worked great, that I could use one
to many without multiple-of-same entries in the table.

Let me experiment with actually doing one to many relationships and
I'll share with you guys what I end up with.

On Oct 12, 8:41 pm, geoffrey hendrey <geoff.hend...@gmail.com> wrote:
> Sounds great! The only drawback to this model is that each word-row is only
> allowed to be a translation from just one language (since each row is on the
> 'one' side of a 'one-to-many'.
>
> However, at the cost of a little denormalization, you can just insert 'cat'
> twice and have one cat-row's translation point to 'gato' and another
> cat-row's translation point to to 'neco'.
>
> A little denormalization goes a long way :-)
>
> -geoff
>

> ...
>
> read more »