Preserving relational integrity with Mnesia

Question

I've been diving into Erlang recently, and I decided to use Mnesia to do my database work given it can store any kind of Erlang data structure without a problem, scale with ease, be used with list comprehensions, etc.

Coming from standard SQL databases, most rows can and should be identified by a primary key, usually an auto-incrementing integer. By default Mnesia considers the first field of a row to be its key. It also gives no way to have an auto-incrementing integer key as far as I know.

Given I have these fictional records representing my tables:

-record(user, {name, salt, pass_hash, email}).
-record(entry, {title, body, slug}).
-record(user_entry, {user_name, entry_title}).

I figure using the username may be good enough for some purposes, as with the entry title, in order to identify the resource, but how do I go about maintaining integrity?

Say the user changes its name, or that the entry's title changes after an edit. How do I make sure my data is still correctly related? Updating every table using the username when it changes sounds like a terrible idea no matter how it's put.

What would be the best way to implement some kind of primary key system in Mnesia?

Also, how would an intermediary table like 'user_entry' do if the first field is usually the key? Otherwise, what would a better way be to represent a many-to-many relationship in Mnesia?

Answer 1

I prefer using GUIDs instead of auto-incrementing ints as artificial foreign keys. There is an Erlang uuid module available at GitHub, or you can use {now(), node()}, given that now/0 doc says: "It is also guaranteed that subsequent calls to this BIF returns continuously increasing values."

Using something that can change as the primary key seems to me to be a bad idea independent of the database system.

Don't forget that you don't need to normalise data in Mnesia even to first normal form; in your example, I would consider the following structure:

-record(user, {id, name, salt, pass_hash, email, entries}).
-record(entry, {id, title, body, slug, users}).

where entries and users are lists of ids. Of course, this depends on the queries you want.

EDIT: fixed to be many-to-many instead of many-to-one.