Byrd — Database Schema v.0.0.1

Every webapp needs a database, right? I believe, there isn’t a single person, that’d try to argue over this statement. But what database, and how to structure — that is where the war begins.

For this project, I use MongoDB with mongoose on the top of it. Mongo is a noSQL, and no-relational database. Although it’s named non-relational, it doesn’t mean you can not have relations around you DB. You can. What you can’t do, is to merge them on the go, you can’t expect the DB to populate the results for you — you can make extra query, or you can specify the query, to populate results for you. But MongoDB, doesn’t do this for you. It definitely has some cons, but overall — DB is easier to scale and gives you (the developer) more freedom. The performance is also much higher, but that’s topic for a separate post.

So, pre-answering the question — yes. I do have relations around by schema. And yes, I am mixing those with embeddable documents feature as well. I should also mention, this Schema probably isn’t in it’s final state, it might (and probably will) change, as I need to think through some aspects.

Collections

Let’s discuss collections I might need for this project. Sometimes the easiest solution are the best one, so… let’s ask myself few stupid questions!

Do I need Users to be able to sign-up?

Yes I do. Users (agents) will be having access to the dashboard, and will answer their clients messages. They need to authenticate.

Messages? So I probably need a Chat collection?

Oh yeah. Every single support ticket, supposed to have it’s own separate document.

But what about Messages? Where they supposed to be stored?

That is tricky question. Technically, nature of MongoDB, allows for using arrays, and I could easily store Messages in it’s Chat document. But the problem is, I don’t want to fetch whole list, of all the messages every single time. I’d like, to fetch them on the go, by 10–20 per query — for instance, when the agent is scrolling up, through the Chat history. But why you may ask — answer is simple: performance. Chat may scale up to even hundreds of messages/events, imagine fetching multiple Chat documents at once, with all their messages. So, that’d be a anti-pattern, you’d stress the DB hardly. Much better solution is to create a separate collection, that’d store all the events, and to create a relation between those documents.

What about clients? Will you store them separately as well?

I thought that through, and I haven’t found any real advantage, of storing ticket authors on separate collection. Therefore, I chose to embed those into Chat document, as 1 chat = 1 client.

Can single User, be an Agent on multiple sites?

Yes. That means I can not simply group users by the URL they’d play a support role as. I’ll need to create a Company collection, which will contain all the company’s details, as well as would store ObjectIDs of all their agents. That way, a User won’t be restricted, to pay Agent’s role on just single site, but will be able to do so, on more than one.

So one User can be an Agent, for multiple companies. What about URLs? Is company restricted to single domain?

No. I thought of that too. Company’s owner would create a Widget, each widget would be use on one, exact domain. Company may have multiple Widgets.

Diagram

That’s what final Schema and its relations would look like.

PS relations seem a bit messy, I think I should change the tool. :)