[erlang-questions] How do you efficiently do this kind of mnesia schema?

[Jeroen Koops]

My first guess would be using three tables, one for users, one for
subitem_a, one for subitem_b. Each table has its own primary key. In
addition, subitem A records have an attribute containing the owning user's
id, which is indexed. Subitem B records have two extra attributes, one
containing the id if the subitem A it belongs to, one containing the owning
user's id. Both are indexed. You can now efficiently query on subitems A
belonging to a user, subitems B belonging to some subitem A, and subitems B
belonging to a user under any subitem A. Do keep in mind that the extra
indexes use RAM though.

Of course, the only way to know for sure is to whip up a quick test, which
is done easily enough.

Maybe not relevant to this particular problem, but keep in mind that indexes
can not only be applied to 'simple' attributes, such as integers or strings
- you can also apply them to tuples. So suppose you have a table containing
documents, identified by a document_id, and a table containing words,
identified by a word_id. To indicate which words appear in which documents,
you have a third table, containing the document_id and the word_id. If you
would need to see quickly if a certain word appears in a document, you could
add a third column, containing a { DocumentId, WordId } tuple, and put an
index on it. Checking if some word appears in some document can than be done
by a single index read.



On Tue, Mar 3, 2009 at 3:29 PM, ryeguy <ryeguy1@REDACTED> wrote:

> I'm trying to break free of the RDBMS thought process, but I can't
> wrap my head around how to efficiently implement this kind of schema.
>
> Say I have a User record, and he has many SubItemA records, which has
> many SubItem B records, so:
>
> User
> -SubItem A
> --SubItem B
> ...
>
> I need to run queries on SubItem B. Is it efficient to do it when it's
> this nested? Should I just normalize it so it will be quicker?
>
> I have heard of some people using data duplication so the data is both
> nested and separate, is this ridiculous or is this actually useful in
> some cases?
> _______________________________________________
> erlang-questions mailing list
> erlang-questions@REDACTED
> http://www.erlang.org/mailman/listinfo/erlang-questions
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://erlang.org/pipermail/erlang-questions/attachments/20090303/619b2500/attachment.htm>