Basic schema questions

[thelucid]

Hi,

I'm just getting started with MongoDB and using Moingoid in a Rails
app. I am building a simple CMS to manage sites for a couple of
clients.

I currently have the following models: User, Account, Site and Page. A
User can have access to multiple accounts, an account can have
multiple sites and a site can have multiple pages. I will normally be
fetching a Site via a subdomain and will need to check that an account
is active/paid for before grabbing a page entry based on the URL.

I'm pretty certain I will keep Users as their own collection as they
can have access to multiple Accounts but my question is, should I
embed Sites within Accounts and Pages within Sites or is deep nesting
like this a bad idea?

Initially my thoughts were to keep Users, Sites and Accounts as top-
level collections and just embed Pages... I would love to hear
experienced Mongo user's views on this. As a side note, when an
Account is first created it should have one Site by default... I would
normally use transactions to ensure this is the case in SQL, does
embedding get around this?


Many thanks in advance,

Jamie

[Karl Seguin]

There's no wrong solution.

I assume an account isn't going to have a ton of sites? Most will have 1 some might have 2-5? If so, I'm tempted to say that you should have:

Users

Account

   -Site

Page

I'm not a huge embedded document fan for anything but small/static pieces of data (like a user's address {street, country, postalcode...})

Karl

[Max Schireson]

How many pages will each site have and how often will they change? If many sites have a lot of pages you might have challenges with really big docs. Also if sizes change a lot and that triggers a lot of document movement that can be inefficient. Without knowing the details I might hesitate before embedding pages in the sites.

-- Max

--
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com.
To unsubscribe from this group, send email to mongodb-user...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/mongodb-user?hl=en.

[thelucid]

A site would on average have 20-30 pages but could potentially have a
lot more. I'm fine with separating the collections as Max suggests but
don't know how to get around situations where I would traditionally
use transactions in MySQL.

An example would be when an account is created, an initial user should
also be created (unless logged in as an existing user) and one site
should be created by default. That's three insertions that need to all
happen somehow, I don't know how I would do this without transactions
unless they were embedded.

[Sam Millman]

You would typically do this through your client side code.

You would use models etc to validate the transaction using safemode inserts so as to get an immediate response from the database to ensure each new row is inserted, if not then bail and queue those rows for deletion.

[Max Schireson]

Can I ask whether the need that these all happen as one atomic unit of work is absolute?

I think for example the creation of a user account needs to be one atomic operation; it is really bad if two users think they've created the account named jamie. But could the application have a bit of logic to handle the case where an account for thelucid is created and creation of the jamie account fails? Most simply could you just delete the account and throw an error if you are unable to create a user and a site, which would be the same as what happens if you do it all in one transaction in an RDBMS? 

Of course you'd want to confirm that all were created before you return some sort of confirmation page to the user, but that can be done relatively simply at the application level. All of this would be very cumbersome if each document was itself 20 or 50 tables, but many find it manageable in part because you are likely to only have a handful of documents involved.

Curious if this line of thinking seems workable for your application?

-- Max

[thelucid]

Hmm, there seems to be a lot that you have to handle outside of Mongo
due to it's lack of transactions. With transactions everything just
works i.e. either all the records are created or they're not. Using
Mongo, if my app code is in charge of checking that everything got
created and the app dies part way through, the database will be left
in an inconsistent state.

My main reason for going with Mongo is that my pages will have various
data i.e. will not follow a rigid schema like with SQL however the
lack of transactions is causing me a headache. My only other option is
to go with SQL and use some kind of EAV system but this makes me
quiver after seeing Magento's schema.

If I'm struggling with the lack of transactions at this stage, I'm
wondering how I'm going to get on if I add a shopping cart with
inventory tracking etc. (a real possibility).

[Sam Millman]

Yea EAV schema would be the biggest mistake you can make. Trying to get a Db to do something it was never designed to do.

Tbh Do you really have to make sure that a user can only be created with a new account? Or can you just make sure that a new site must be made with a new user? Because even if you can't make that user and site the person using the computer would not prolly want to redo their account information because of that and in reality would still want their account created.

[Sam Millman]

In fact if you wanted to be uber intelligent about this you could store the failed details of the user and site and tell the user their account could be created (log them in ofc) and then show a "create site" form pre-filled with the failed details and waiting for corrections.

Ofc managing the full transaction client side is not that bad, the ActiveRecord pattern fits here perfectly and can even handle trigger cascade deletions on other rows.

[thelucid]

With the risk of sounding stupid, what is ofc?

A user can exist in his/her own without an account however when a user
signs up an account and site should be created as a site belongs to an
account and a user can have access to multiple accounts. Therefore for
a user to have access to edit a site, the site and account must exist.

On Jan 26, 11:37 am, Sam Millman <sam.mill...@gmail.com> wrote:
> In fact if you wanted to be uber intelligent about this you could store the
> failed details of the user and site and tell the user their account could
> be created (log them in ofc) and then show a "create site" form pre-filled
> with the failed details and waiting for corrections.
>
> Ofc managing the full transaction client side is not that bad, the
> ActiveRecord pattern fits here perfectly and can even handle trigger
> cascade deletions on other rows.
>

[Sam Millman]

Sorry I'm so used to using short hand at times I forget to write full hand when talking to others who might not know what I mean; "tbh" = to be honest.

ok it sounds like it goes like:

user -> account -> site

So user would be some company individual while account would be, say, a company they own and the site would be their company site for that company; am I right in this assumption? If so you can just change my advice to put sites as embedded into accounts.

[thelucid]

I still don't know what "Ofc" means ;)

That summary is correct although a user can also have access to other
account's sites by invitation. In essence a User is global to the
whole system but an account has only one "owner" user. Based on this,
a user can have multiple accounts that they are responsible (are
owners) for but can also edit sites on other user's accounts by
invite.

So, are you suggesting the same as Karl? e.g.

User
Account
- Site
Page


Many thanks

[Sam Millman]

Haha, im my hastyness I said the meaning of tbh instead of ofc; ofc = of course.

By the looks of his post (though a little bare) yes. The relationship of invites for one user to co-ordinate with another account can be done quite easily with non-transactional updates, that is more upto the interface as to exactly how you make this work.

[thelucid]

Thanks Sam, that clears thing up a bit. In that case, to summarise
I'll go with something like this (someone please chip in if this is
blatantly wrong and sorry about the long post):

{
users: [
{ id: 'user1', email: 'bill[at]companyone.com' },
{ id: 'user2', email: 'ben[at]companytwo.com' }
],

accounts: [
{
id: 'account1', name: 'Company One', owner_id: 'user1',
sites: [
{ id: 'site1', subdomain: 'company-one-first-site',
editor_ids: ['user1'] },
{ id: 'site2', subdomain: 'company-one-second-site',
editor_ids: ['user1', 'user2'] },
]
},
{
id: 'account2', name: 'Company Two', owner_id: 'user2' ,
sites: [
{ id: 'site3', subdomain: 'company-twos-only-site',
editor_ids: ['user2'] }
]
}
],

pages: [
{ id: 'page1', title: 'Homepage one', site_id: 'site1' },
{ id: 'page2', title: 'Homepage two', site_id: 'site2' },
{ id: 'page3', title: 'Homepage three', site_id: 'site3' }
]
}

Based on this, bill[at]companyone.com owns 'account1' and can
therefore edit both sites on his account (site1 and site2).
bill[at]companytwo.com on the other hand can edit the site on his own
account (site3) but also the second site on bill's account (site2).

The only questions I have left now, if this all looks sane are should
I store the editor_id's in a site (as the example) or would I be
better storing the site_id's in a user for the sites he's allowed to
edit? Also, should I embed the pages within the sites?


Thanks again,

Jamie

> ...
>
> read more »

[Barrie]

Hey Jamie,

These two questions depend on the nature and behaviour of your
application. For example:
If you store "editor_ids" in a site, then it will be easier to answer
things like "for site X, this site can be edited by persons A, B and
C". On the other hand, by storing editable site_ids in a user
document, it will be easier to build logic around "person Q has access
to sites X, Y, and Z". So depending on which scenario comes up more
often in your app, you might choose one over the other.

As for embedding pages within sites, you should choose based on how
frequently they will be modified, added, or deleted (as Max pointed
out). If you need to do more complex queries on actual pages or if
they will be updated heavily it might be more performant and flexible
to keep it in a separate collection. Otherwise, you might end up
relying very heavily on the array $ operators, which can be more
limited.

Hope this helps.

Barrie

> > > > > > >> > > > > multiple sites and a site...
>
> read more »

[thelucid]

Hi Barrie,

That makes sense, it's a whole different way of thinking to SQL.

I've decided to keep pages separate as they tend to be queried on
their own quite a bit and if they were embedded within sites then
every time I wanted a page, the entire pages collection for the site
would have to be retrieved.

As for editor/site_ids, I'm still undecided on that as not worked out
the most common use-case but your comments help to clarify things.


Thanks,

Jamie

> > > > > > > >> > > should be created by default....
>
> read more »

[Barrie]

Hey Jamie,

Thanks for sharing this info! Glad the comments were of use to you.

Barrie

> > > > > > > > >> > part because you are likely...
>
> read more »

[Sam Millman]

Personally I would not have embedded pages with sites either only users within accounts (if I remember right), I lost track of this question and it got to the bottom of the list in time so yea sorry about not replying :(.

>
> read more »