This is about how I learned to make a real database schema. It was my first exposure to roles and the generally more robust practice of “multiple inheritance” object-oriented database design. This is one wheel I would never have reinvented on my own, either.

So, I had no clue what I was doing, really.

My data “model,” or my “domain model,” if it can even be called that, was nowhere near relational. It wasn’t even a data-warehouse model. It was just a bunch of stuff in some tables. There were no foreign key constraints.

I had to manually (in the application, which was no where near MVC) update everything to maintain the merest shred of “referential integrity.” In virtually every Mason component there was a huge <%init> block that not only pulled in data, but did maps, sorts, extractions, validation, etc. I might as well have been grepping through spreadsheets. I remember the day in early May 2007, when someone I considered a hardcore Perl programmer told me, “Oh, you’ll see. My ORM keeps it real simple. You do all that stuff application side.” Ah, hubris.

Here’s how it used to look:

Here’s how it looks now:

Wow. You can’t even really read the newer graph it’s so dense and interrelated.

The thing is, the current model grew organically, and I was just updating my MySQL tables and my class files manually for every additional column and accessor. After 18 months of that, adding new features, expanding this, modifying that, I had grown myself an unmanageably large helping of spaghetti. Everything was application side, remember? No constraints in the DB, no transactions.

I knew I needed some help. I hired shadowcat, and they told me first to send them my current domain model. I did. They pretty much threw it out and we rewrote it from scratch.

Let’s take a couple of objects as an example.

• User
• Artist
• Album
• Metalabel
• Track
• Label
• Scout
• Blog
• Photo
• Friend

I’m only going to talk about a couple of these in detail, but you’ll get the idea. This first thing phaylon noticed was that a user can do a couple of different things. A user can be an artist or a label and a user can create a metalabel. A metalabel does a subset of the functions of a user: it can make friends and it can post blogs and some other stuff. mst said “Smells like a role.” phaylon agreed, and came up with a couple of class declarations:

role Creator {
    has blog_entries_about isa ArrayRef[BlogEntry];
    has blog_entries isa ArrayRef[BlogEntry];
    has bulletins isa ArrayRef[Bulletin];
    has comments_about isa ArrayRef[Comment];
    has comments isa ArrayRef[Comments];
    has discussions isa ArrayRef[DiscussionTopic];
    has events isa ArrayRef[Event];
    has friend_requests isa ArrayRef[Friend] where is_accepted is false;
    has friends isa ArrayRef[Friend] where is_accepted is true;
    has photos isa ArrayRef[Photo];
    might have style isa CustomStyle;
    has creator isa ArtistIdentity |
                    Band |
                    Label |
                    Metalabel |
                    Scout |
                    User;
}

This Creator role will be consumed by all 5 classes that can “create” stuff. Those classes are ArtistIdentity, Band, Label, Metalabel, Scout, and User. Each of those classes have their own definitions. For instance:

class User does Addressable, Artworkable, Bloggable, Commentable, Creator, Discussable, Pollable {
    has artist_identities isa ArrayRef[ArtistIdentity];
    has credit isa CurrencyValue;
    has messages_out isa ArrayRef[Message::Private] where sender is me;
    has messages_in isa ArrayRef[Message::Private] where recipient is me;
    has messages_out isa ArrayRef[Message::Bulletin] where sender is me;
    has messages_out isa ArrayRef[Message::Bulletin::Friend] where sender is me;
    has rsvps isa ArrayRef[Event::RSVP];
    has metalabels isa ArrayRef[Metalabel];
    has labels isa ArrayRef[Label];
    has playlist isa Playlist;
    has address isa Address;
    has favorite isa Genre | Subgenre;
    might have open_id isa OpenID;
    has mail_preferences isa MailPreference;
}

In the User class, we first define the roles it consumes. You can see it consumes multiple roles, among them, Creator. So far, this is fairly clear, I hope. It’s just a sort of MooseX::Declare-like pseudocode now, but the basic idea is that every class does stuff. Some classes do the same stuff. That is a role. Some classes are really just instances of other superclasses. Like the Label, for instance:

class Label isa User, does Addressable, Bloggable, Creator, Commentable, Discussable, Genred, Pollable {
    has address isa Address;
    has artists isa ArrayRef[Label::Artist];
    has billing isa Billing;
    might have paypal_id isa PayPalID
    has company_name isa Str;
    has is_incorporated isa Bool;
    has corp_state isa Str;
    has tax_id isa Str;
}

The real mind-bending stuff for me came when we actually began implementing this stuff in SQL. The most important conceptual thing for me was how everything that consumes a role has to have a unique ID. Because a role is implemented in SQL as a primary-key only table!

create table creator (
    id integer primary key not null
);

Obviously, if 5 classes are going to be consuming this role, you cannot just start each class’s table with “id serial primary key not null,” because then you coulnd’t look up a Creator by its ID. Ther would be multiple Creators with the same ID, and your database would be useless.

So all role consuming classes share a sequence:

create sequence entity_seq start 1;
...

create table artist_identity (
    id integer primary key not null default nextval('entity_seq'),
    date timestamp not null default 'now',
    "user" integer not null,
    name text
);

This doesn’t only go for things like Creators, which are “active” tables, but also for things like Albums, which can be acted upon. My Album in Moosey pseudocode is:

class Album does Artworkable, Bloggable, Commentable, Discussable, Genred, Pollable, Videoable {
    has tracks isa ArrayRef[Track];
    has artist isa MusicProvider;
    has title isa NonEmptySimpleStr;
    has coverart;
    has notes isa Str;
    has is_deleted isa Bool;
    has date isa TimeStamp;
    has creation_date isa Date;
    has album_only isa Bool;
    has free isa Bool;
}

So, the Album object does 7 roles. It can be acted upon in 7 ways. It obviously also needs to share in the database-wide unique sequence, so I can look up any particular, say, Bloggable item by its ID:

create table album (
    id integer primary key not null default nextval('entity_seq'),
    date timestamp not null default 'now',
    provided_by integer not null,
    title text,
    creation_date date not null,
    notes text,
    album_only boolean,
    free boolean,
    deleted boolean
);

Finally, each object that consumes any role needs to have foreign keys to that role. So, the Album object needs a FK to each role it consumes. It needs 7 Fks:

alter table album add foreign key (id) references artworkable (id);
alter table album add foreign key (id) references bloggable (id);
alter table album add foreign key (id) references commentable (id);
alter table album add foreign key (id) references discussable (id);
alter table album add foreign key (id) references genred (id);
alter table album add foreign key (id) references pollable (id);
alter table album add foreign key (id) references videoable (id);

My User object needs 8:

alter table "user" add foreign key (id) references addressable (id);
alter table "user" add foreign key (id) references artworkable (id);
alter table "user" add foreign key (id) references bloggable (id);
alter table "user" add foreign key (id) references commentable (id);
alter table "user" add foreign key (id) references creator (id);
alter table "user" add foreign key (id) references discussable (id);
alter table "user" add foreign key (id) references pollable (id);
alter table "user" add foreign key (id) references videoable (id);

This is to make updating your tables across views easier when records are created or deleted.

I still don’t fully understand that bit, so I’ll try to write about it next time. Meanwhile, if you’re not doing something like the above, and you find yourself doing lots of manual record updates in your code, you’re not using your database effectively!

• BROWSE / IN TIMELINE
• » I’m In Ur Loader

• BROWSE / IN databases perl web development
• » I’m In Ur Loader