Explaining Database Schema

At Socialveo we don’t like to repeat ourselves. We also like simple things that work. That is why we put a lot of effort in creating a database schema for Socialveo that will be flexible, concise and performant.

We’ve found along the way that this comes at a cost of “ease of understanding” as some concepts we have used are not immediately understood by users who see our db setup for the first time.

Luckily it’s not THAT hard to understand.

IDs and UUIDs

On each table we use an unsigned integer based ID and an additional UUID (universally unique identifier). You can read about UUIDs here.

So don’t be surprised if you see a table like this:

 |----|--------------------------------------|------------|-----

 | id |                 uuid                 |    name    |  ..

 |----|--------------------------------------|------------|-----

 |  1 | e638eba4-3557-11e5-9f90-00163d01016e | Golf Club  |  ..

 |  2 | a4e3b3c8-3557-11e5-91ed-00163d01016e | Dancers    |  ..

We tend to use the integer (“regular”) keys as the native database PK as FK, but we never communicate either of the (id or …_id properties) via API to clients. We believe that exposing incremental, numeric identifier in public APIs just gives away to much information to the public at large.

So a network resource of ID=1 stored in a database table above in Socialveo would be available via:

GET /networks/e638eba4-3557-11e5-9f90-00163d01016e

 

Polimorphic Associations

A lot of times we come across a situation when resource is linked to many other resources of different type. Enter polymorphic associations. You can read more about them here.

We use polymorphic associations a lot. An example spam resource informs that some content was marked as spam by a user. Content can be either a post, comment, media, or even custom-type. Here is how it looks like in our spam table:

     

|----|--------------------------------------|-----------|--------------------------------------|-------------|---

| id |                 uuid                 | target_id |              target_uuid             | target_type | ..

|----|--------------------------------------|-----------|--------------------------------------|-------------|----

|  1 | b6109a8a-3557-11e5-b053-00163d01016e |    8341   | a4e3b3c8-3557-11e5-91ed-00163d01016e |   post      | ..

|  2 | 9f766ade-3557-11e5-98d6-00163d01016e |    6430   | b1ae69e0-3557-11e5-8c1d-00163d01016e |   comment   | ..

|    |                                      |           |                                      |             |

 

Meta Columns

It’s pretty handy to know when a given record in the database was created or updated and is it active (for soft-delete). That is why we add these three columns to every our database table. These columns are updated by Socialveo automatically.

If you peek inside any of the table you might see the last three columns are always create, udpated, active:

---|------------|------------|--------|

.. |  created   |   updated  | active |

---|------------|------------|--------|

.. | 1438108615 | 1438108622 |    1   |

.. | 1438108616 | 1438108624 |    1   |

.. | 1438108616 | 1438325921 |    0   |

JSON Data Columns

Seeking an efficient yet simple way to store, custom, complex data about the resource, we decided to take introduce special columns that hold JSON serialized data.

Most of the times it is the properties column:

|----|--------------------------------------|--  --|---------------------------------------|---

| id |                 uuid                 |  ..  |                properties             | ..

|----|--------------------------------------|--  --|--------------|------------------------|----

|  1 | b6109a8a-3557-11e5-b053-00163d01016e |  ..  | {"menu":{"id":"file","value":"File .. | ..

|  2 | 9f766ade-3557-11e5-98d6-00163d01016e |  ..  | {"value":"Close","onclick":"Close" .. | .. 

|    |                                      |      |                                       |

This gives us and the developers community the freedom to store almost any arbitrary data on the record without the need to make additional queries/calls.

Of course, data store in properties (and other columns of this like) is not filterable, but even though the solution proved to be very handy.

Network as general concept

You would think a network to be an equivalent of a User group in LinkedIn. And yes. It could be. But could also be something more.

We designed the table network to be a rather general container which has three important aspects:

  1. it is created by a given user (user_id, user_uuid in network table)
  2. Network types (profile, group, business, events, pets, etc.)
  3. content can be attached to it (setting the network_id, network_uuid on the given content piece)
  4. users might connect to it (through connection table, filling out the network_id, network_uuid)

While at first this might sound crazy. If you think about it a little more, you will start to see it make more sense.

Network as a general concept allows us to use the network table to store information on:

  • profile – belongs to a user, posts can be posted on the profile page, other users might follow or be friend.
  • group – created by a user, posts can be posted on the group page, other users might join or become members of.
  • business – created by user who want to promote their business
  • custom – anything custom such as events, pets, cars, brand-pages, etc.

Each network types can have custom fields thanks to additional tables created for that purpose. Socialveo offer by default different types of networks.

We’ve found this approach creates less complexity on both the Frontend and Backend side. With this one single interface (/networks, /posts, /connections) we can achieve quite a lot, while keeping our codebase simple, clean and concise.

Post as general concept

The same thing as with networks holds true for posts in our case. So a post isn’t just a typical micro-post – for us it is a more general piece of content a user published to a network. You may see this also in other software such as WordPress.

What this means is – posts can be of different type. Our post could be a micro-post but it also be a video, an image, a poll, a HTML5 game, a To Do List, you name it. We put no limit here. The type of post is represented by a machine-readable (chars only) string in the post_type column:

|----|--------------------------------------|--  --|-----------|-------------|---

| id |                 uuid                 |  ..  | post_type |    body     | ..

|----|--------------------------------------|--  --|-----------|-------------|----

|  1 | a0da08cc-3557-11e5-8cec-00163d01016e |  ..  |    post   |     ...     | ..

|  2 | a15bdf14-3557-11e5-a71d-00163d01016e |  ..  |    image  |     ...     | ..

|  3 | a74a42d0-3557-11e5-b7bd-00163d01016e |  ..  |    video  |     ...     | ..

|    |                                      |      |           |     ...     | ..

Connection as general concept

The missing piece – the linkage between network and user – is in our case represented by connection resources. Each connection holds a reference to a user and a network to which the user is connected in some way. The flags of the connection informs us what type of the connection it is:

  • pending (flat: 1) – waiting for the target network owner to accept
  • blocked (flag: 2) – marked as blocked by the target network owner
  • forgot (flag: 4) – target network owner has no made a decision to accept the connection
  • connected (flag: 8) – connected to the target network (deprecated)
  • confirmed (flag: 16) – accepted by the target network owner
  • invited (flag: 32) – invited by the target network owner
  • follow (flag: 64) – connection type follow (one-way connection)

Connection Groups (Circles)

Each connections can be added to specific group of connections. You may see this similar concept in Google+ Cirlces. Default Socialveo connection groups are: Following, Friends, Family, Co-workers. You can change this via configuration and also users can create custom connection groups.

If you want to know more about Socialveo database scheme, check here Socialveo database table columns.

Happy Coding!

Was this article helpful?

Related Articles

Leave A Comment?

You must be logged in to post a comment.