The ACM Multimedia Conference is a premier conference for multimedia experts and practitioners across academia and industry. The conference is held at the Lotte Hotel in Seoul, Korea from 22-26 Oct 2018. As the co-author of the paper – SLIONS: A Karaoke Application to Enhance Foreign Language Learning, which was accepted for oral presentation, I had the privilege of attending the conference under an SoC grant.

The conference is an extensive program that includes technical sessions covering all aspects of the multimedia field in forms of oral and poster presentations, tutorials, panels, exhibits, demonstrations, videos, doctoral symposium, and workshops, as well as Grand Challenge Competitions and Open Source Software Competitions. The program covers important foundations, as well as challenging and novel topics in the multimedia field. I will be covering a few presentations that I find particularly interesting below.

Context-Aware Unsupervised Text Stylisation

In this work, the researchers presented a novel algorithm for unsupervised text stylisation. The main structural imagery of the source image is first extracted to create an initial mapping, which is then refined by a legibility-preserving structure transfer algorithm that adds shape characteristics to the text. As a result, the framework is able to automatically transfer the text style to a target image with high accuracy, without the need for ideal input as required by supervised methods.

The framework has many real-world applications, including photography post-processing and graphic designing.

Polygon Annotation of fashionable clothings

Recognising different pieces of clothings from an image has been a challenging computer vision problem as they varies greatly in appearance, patterns, layering and position.
Yet, with the proliferation of E-commerce, the ability to detect and classify fashion item has become extremely crucial for functionalities such as visual search and product recommendation.

In this work, the researchers collected a million street fashion photos, filter out unsuitable photos (eg. low quality) and annotate the rest, through application of various deep learning techniques, including Faster R-CNN, ResNet, SSD and YOLO.  This annotated dataset will also be extremely useful for further researches on suitable training models for fashion item detection.

Oral and poster presentation

We presented our paper in our oral and poster presentation session. There were many invaluable insights and suggestions raised by the audience, which will be helpful in deciding our next step forward for the project.

Conclusion

Through the conference, I have gained deeper insights into all aspects of the multimedia field in Computer Science, as well as exposure to the frontier of multimedia research and industrial innovations. Through the interactions with experts and practitioners from all over the world and across academia and industry, I have also developed a more holistic view of Computer Science research. Overall, the conference has enhanced my competencies in presentation and research skills, as well as broadening my horizons on the potential of multimedia research in real world applications.

When using promise-mysql, we often want a singleton connection created in a .js file:
module.exports = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'mydb',
})


then files for different routes will import and use it:
const connection = require('./connection');
router.get('/', (req, res) => {
  connection
    .then(conn => conn.query('SELECT * FROM table WHERE id = ?', req.body.id))
    .then(rows => res.send(rows));
});

However, calling connection.then(conn => ...) for every routing function is somewhat troublesome.

In ES6 modules, variables are exported as immutable bindings rather than values. Hence, we can do the following:
let conn;
mysql.createConnection({
  host: 'localhost',
  user: 'root',
  database: 'mydb',
}).then((connection) => { conn = connection; });
export { conn as default }

Now we can use conn directly in routing functions:
import conn from'./connection';
router.get('/', (req, res) => {
    conn.query('SELECT * FROM table WHERE id = ?', req.body.id))
      .then(rows => res.send(rows));
});
How cool is that? :p

As even Node 9 does not support ES6 modules, Babel has to be used to transpile the source, which wraps the exported value as an object to achieve to same effect.

Note: for some peculiar reason, the export default conn syntax does not export conn as live binding.

I first started mobile development in native iOS with Swift 3.0 and XCode 8.0. Swift was a very new language with fancy syntactic sugars such as trailing closures, type inference etc. With the exception of string manipulation, I generally enjoyed coding in Swift.

The same cannot be said for XCode. It’s arguably the worst IDE I ever had to use. Major issues include random hangs/crashes (especially with the Storyboard), slow compilation time, and bad user interface (for one, I frequently misclicked on breakpoint), broken/strange auto-format rules. It didn’t even have code renaming/refactoring back then!

As for Android, needless to say, most people avoid Java if they have a choice.

Fortunately, React Native soon became mature enough to replace native app development.

A React Native app is a real mobile app

With React Native, you don’t build a “mobile web app”, an “HTML5 app”, or a “hybrid app”. You build a real mobile app that’s indistinguishable from an app built using Objective-C or Java. React Native uses the same fundamental UI building blocks as regular iOS and Android apps. You just put those building blocks together using JavaScript and React.

Benefits of React Native:

1. Very similar to React.js. Learn once, write everywhere!
2. Most of your code would run on both Android and iOS perfectly. Write once, run everywhere!
3. More reusability between mobile and web version. If you have written some front-end logic in Javascript, you can easily share it between your mobile and web versions. Even UI components could be made to be reused through react-native-web, though native component designs might not be suitable for your web version.
4. Much better development environment! With live/hot reloading, you can see your changes in <1 sec instead of waiting several seconds for XCode/Android Studio to compile!
5. Retain the flexibility to incorporate native modules when necessary.

To  create a React Native app, we have 2 choices:

1. react-native init: a standard and bare minimal React Native app
2. create-react-native-app: the native parallel to create-react-app. Comes with Expo SDK.

More on Expo SDK

Expo apps are React Native apps which contain the Expo SDK. The SDK is a native-and-JS library which provides access to the device’s system functionality (things like the camera, contacts, local storage, and other hardware). That means you don’t need to use Xcode or Android Studio, or write any native code, and it also makes your pure-JS project very portable because it can run in any native environment containing the Expo SDK.

Expo also provides UI components to handle a variety of use-cases that almost all apps will cover but are not baked into React Native core, e.g. icons, blur views, and more.

Finally, the Expo SDK provides access to services which typically are a pain to manage but are required by almost every app. Most popular among these: Expo can manage your Assets for you, it can take care of Push Notifications for you, and it can build native binaries which are ready to deploy to the app store.

While the pitch makes it sound like the holy grail to React Native development, it does come with some drawbacks (un-ejected):

1. No access to native modules
2. No asset bundling (assets have to be downloaded online from Expo Cloud when user first open the app). Asset bundling feature is planned in the next release though (v24.0)
3. Larger app size (from my own experience, it increases the size by ~30 MB for iOS and ~8MB for Android)

 Microsoft Code Push

Expo comes with “Over the Air” updating, meaning if you did not alter any native codes (not possible for an unejected Expo app anyway), you can deliver changes in your Javascript code instantly to your user the next time they restart the app, instead of waiting ~2 days for App store review of your updated app!

However, if you choose not to use Expo for the above or other reasons, then Microsoft Code Push is the tool of choice in distributing your app over the air.

Apart from the instructions in the documentations, I had to add google() to gradle’s buildscript/repositories for Android, and set Code Signing Style to Manual in Build Settings for iOS, for App Center to successfully create a signed build.

As seen above, it comes with complete CI/CD pipeline, auto building, testing and distributing your app whenever a new commit is made. Additional functionalities include crash reporting, analytics and push notifications! Yet another great addition to my automation tool belt!

For almost any apps, there is a need to persist user settings. Ideally, these settings should be persisted both on server side and locally so that it can be synchronised across device and retained for offline usage. Even if you do not intend to offer offline functionalities, you’d still need to store the authentication token locally to use it to fetch the matching user’s settings from the server.

One possible solution is to use Firebase, which provides a realtime database specially designed for this purpose.

However, if you do not want to tie down your app to Firebase, or if your app only requires local storage, then you can manage it on your own through the LocalStorage interface provided by the Web API, or AsyncStorage in the case of React Native apps.

If you are storing all user settings in the Redux state, then there is an easy solution: redux-persist. With just a few simple API calls (well documented in the Github repository), it’d automatically persist your redux state locally after every state changes and rehydrates it when user reopens the app.

In the recently released version (v5), it added cool new features such as delaying the rendering until rehydration is complete, adding versions to the persisted state for migration when state tree structure is updated.

If there’s any part of the Redux state tree that you do not want it to persist (most commonly the routerReducer), then you can make use of the blacklist/whitelist config. If you need to blacklist/whitelist a nested state in a reducer, you might need to add in the redux-persist-transform-filter package.

In this post, I’ll outline the tools I used to automate Continuous Integration and Continuous Delivery of a web application so that every team member can focus on what’s important: writing code.

1. Continuous Deployment of front-end

With Git/Github driving continuous integration, how can we automate the process of building the source from last commit and deploying it to the live website (for the production/master branch)?  My previous tools of choice were Github pages + Travis, but Yihang introduced to me a more feature-rich solution,  Netlify:

Netlify is an all-in-one platform for deploying and automating modern web projects.

Simply push and Netlify provides everything—servers, CDN, continuous delivery, HTTPS, staging environments, prerendering, asset post processing, DNS, and more.

Any project, big or small, can perform instantly on a global scale.

With Netlify, every time you make a commit to the production branch, the configured build command will automatically be executed and the build folder will be deployed to a subdomain provided by Netlify (eg. vis.netlify.com).

2.  Continuous Deployment of back-end

I host my back-end (which serves RESTful API to the front-end) on AWS EC2. I make use of Travis CI to build the source (not needed for NodeJS apps not using transpiler) and upload it to Amazon S3, and trigger new deployment on AWS CodeDeploy whenever a new commit is pushed to Github. Lastly, for NodeJS apps, I run pm2 with the command

pm2 start ./bin/www --log-date-format='YYYY-MM-DD HH:mm:ss' --watch

for logging and to automatically restarting the server when CodeDeploy uploads new build files to the EC2 instance.

3.  Custom domain

The domain name registrar I’m using is namecheap.  For the back-end, I make use of AWS ELB‘s Certificate Manager for provision of the SSL/TLS certificate.

As ELB does not have a static IP address, we cannot use A record to point namecheap domain to the ELB directly.

The solution is to set namecheap’s name servers to AWS Route 53‘s to use it as the custom DNS manager and give AWS complete control over the domain. Then, I create an A record in Route 53 to point to the ELB target, which forwards traffic to the EC2 instances.

For the front-end, I created CNAME records in Route 53 to point the custom domain to the subdomain provided by Netlify.

My final Route 53 Hosted zone configurations:

As Route 53 is not completely free (though it’s dirt cheap), a workaround to avoid using Route 53 is to create a subdomain (eg. www.example.com) to point to the ELB with a CNAME record (which root domains are not allowed to have), then use domain forwarding to forward example.com traffic to www.example.com. The disadvantage of this approach is that it makes your website URL slightly longer (“www” is enforced).

4. Forced HTTPS

Netlify supports automatic TLS certificates with Let’s Encrypt.

To redirect HTTP traffic to HTTPS for the front-end, we can simply configure it under Netlify’s domain management HTTPS settings:

One key advantage of Redux is making the app’s state mutation obvious, predicable and easy to manage.

Yet, in a complex application with several connected / asynchronous actions, debugging is still an inevitable eventuality.

Since the dawn of programming, debugging has been a tedious process that plagues every software developers. A myriad of debugging tools has been developed to ease the pain, at varying success rates.

In this post, I’ll explore our available options in debugging a Redux web/mobile application.

1. Manual logging — the naive way

If a reducer does not appear to be working, we can simply add console.log statements to print the previous state, action and the next state:
case EDIT_COMMENT: {
  console.log('prev state', state, 'action', action);
  const copy = state[action.questionId].slice();
  copy.some((comment, index) => {
    if (comment.commentId === action.commentId) {
      copy[index] = { // create a new obj as comment is not deep-copied
        ...comment,
        text: action.text,
        date: action.date,
      };
      return true;
    }
    return false;
  });
  console.log('next state', { ...state, [action.questionId]: copy });
  return {
    ...state,
    [action.questionId]: copy,
  };
}


Obviously, it’s rather tedious to do this every time u want to debug a reducer, and sometimes you might not be able to pinpoint which reducer is problematic, in which case you might end up manually adding it for all your reducers.

2. Redux-logger  — the lazy way

Redux-logger is a middleware that basically does the above process for you automatically: prints all Redux state changes and logs them.

To use it, we simply add it to our middleware

import logger from 'redux-logger';
const middleware = [thunk, routerMiddleware(history)];
if (process.env.NODE_ENV === 'development') {
  middleware.push(logger);
}
const store = createStore(reducer, applyMiddleware(...middleware));


And every state changes will now be logged to the console:

This is convenient, but it makes your other console.log statements hard to spot (unless you use a different level of logging for the rest of your applications, say console.warn). Furthermore, you have to examine the state one by one and figure out which part of the state changed manually.

3. Redux-devtools — the smart way

Like redux-logger, redux-devtool also logs all the state changes, except that it presents in a separate window with a much nicer GUI:

To use it, we simply download the extension and add it as a composer in `createStore`.

const composeEnhancers = (process.env.NODE_ENV === 'development' && window.__REDUX_DEVTOOLS_EXTENSION_COMPOSE__) || compose;
const store = createStore(
  reducer,
  composeEnhancers(applyMiddleware(...middleware)),
);


Or in React Native environment:
import { composeWithDevTools } from 'remote-redux-devtools';
...
composeWithDevTools(applyMiddleware(...middleware)),
...


As seen in the above screenshot, the tool also compute the state diff after every action. It also comes with various additional features such as time-travelling*, state tree overview and a nice chart:

As cliché as it sounds, this is an essential tool for all Redux developers!

4. Redux-logger-server — the masochistic way

This is a tool that does the exact same thing as redux-logger, except it logs to your server instead.

Why would anyone want to pollute the server log with redux state changes?

I supposed its only purpose is to try tracking down a bug that your clients are reporting but neither of you could replicate it deterministically.

Assignment 1

Assignment 1 has come to an end and this is the final product we ended up with: https://www.nuseats.club/

Some things I have learnt from this assignment:

1. Various css/javascript tricks for mobile responsiveness (eg. the use of window.onresize to make Twitter Bootstrap’s ButtonGroup horizontal when overflown to next line since some component props cannot be altered by CSS alone)
2. Redux’s mapStateToProps gets invoked for all components whenever any part of the redux state tree changes, then a shallow comparison between the new props and previous props is done to determine if component should be re-rendered (after the additional shouldComponentUpdate check)
3. null and undefined have very significant difference when it comes to optional parameters, as null overrides the default argument in Redux reducers while undefined does not. Hence in initial hydration of the store (eg. from local storage), fields that are not set should be set to the value of undefined rather than null so that default arguments don’t get overridden.
4. The trade-off between storing all states in Redux store vs keeping UI-specific states within local components. The former allows time travel debugging and caching if desired, while the latter is simpler to implement/modify (skip all the boilerplates to interact with the Redux store) and keeps the Redux state tree cleaner.
5. Implementation of routers for modals and collapsible panels in order to allow Facebook sharing integration.
6. The intricacies of presentational and container components pattern. Eg. when a stateless functional component contain containers, it might affect the reusability of that component. Hence Redux’s ubiquitous store should not be abused and sometimes it’s still better to pass down props from parent components to child components (instead of child components getting state from Redux store directly) to enhance reusability.

Assignment 3

For assignment 3,  our group realised we have a common problem of missing the train/bus stop to align as we were too focused on our phone/dozed off. Furthermore, it’s very inconvenient to check the google maps/look outside the window constantly to see if we have arrived at the intended bus stop.

To solve this problem, we wanted to create a mobile app in React Native that automatically triggers a mobile notification/alarm when we reach a destination.

The destination will be manually set by the user initially (through selecting the MRT/bus stop name) but the setting will have an option to be recurred so that users will not have to set it manually every time before a trip.

There will also be smart detection to make sure it only alerts when user is actually arriving at destination instead of leaving the destination. (eg. alerts when user arrive at Com 2 bus stop from Clementi, but not when user is at com2 bus stop but is actually leaving for Clementi)

Unfortunately, the teaching team limits assignment 3 to pure PWA  (Progressive Web App) only and disallows React Native.

A PWA is not able to retrieve user’s geolocation when the browser is closed. There was a specification to add Geofencing to service workers but it was abandoned 🙁

As a result, we have to make adjustment to the idea. The user will now have to activate the app every time before the trip instead of automated recurrence. Thankfully, Web Push API has been implemented in all major browsers. Hence, our users will be able to receive push notifications on their mobile even when the browser is closed and the phone screen is turned off.

http://www.cs3216.com/coursework/seminar/

Group presentation for the application critique has concluded and our next task is to summarize and critique on another group’s presentation. I have been assigned to critique on Group 6’s presentation on Mendeley. I will briefly highlight 3 main points presented by the team and give my own thoughts about them.

Main point 1: One app to rule them all

Mendeley is a one-stop solution to aid researchers in writing and publishing their research papers from scratch to end. The presented team did not have time to go through it in detail but I will highlight it step by step here:

1. Ideation: Through their social platform encompassing a global research community, one will be able to easily find research partners and project ideas to work on
2. Data searching: With their searchable library containing public research papers, one can easily to mine and find relevant data from its big data repository
3. Data storage and organization: Researchers can store their collected data on Mendeley’s cloud storage, allowing access from anywhere, as well as version control (think Git) to allow researchers to keep access to all versions of their datasets, which is particularly useful for longitudinal studies
4. Data processing: Mendeley allows one to conveniently annotate on the research paper and datasets, as well as linking articles to the data
5. Data sharing: Researchers can share data among each other, increasing the availability of datasets
6. Citation generation: Allows researchers to generate citations with just a few clicks
7. Peer review and discussion: Researchers can review and critique each other’s research paper before it gets published
8. Research profile: Researchers can create a research profile with a compilation of all their publications and affiliations.

In summary, Mendeley is a single platform that makes the tedious process of publishing research papers manageable and efficient.

This process draws a parallel between that of publishing a research paper and publishing a software application. In the software development lifecycle, one must go through the daunting process of collection of facts, analysis, design, development, integration, testing, deployment and evaluation.  Various tools have been created to aid in version control, continuous integration, continuous delivery and continuous deployment.

My assignment 1 team currently employs a variety of tools for this entire process, including Google docs, Github, Trello, Invisionapp, Apiary, Netlify, AWS, Travis, Selenium, SourceTree, IDEs, CLI for npm, React Developer Tools etc. Evidently, I have to manage and switch between so many apps, hindering my ability to develop efficiently.

Yet, a panacea for a process as complex as software development might never exist. Developers have a wide range of preferences and current tools in the market are well suited only for their targeted niche. Any attempt to produce a one-stop solution to software development will prove arduous even for software giants like Google.

Main point 2: Inconsistency across different platforms

As the presented team highlighted, the application is inconsistent across its supported platforms (Desktop, web and mobile), not just in terms of design but also functionalities. Eg. some functionalities are present on one platform but not another.

I wholeheartedly agreed that this inconsistency is rather undesirable. I often come across responsive mobile web apps that hide certain functionalities and I’m forced to select ‘request desktop version’ then pan and zoom around to access the desired functionalities.

While I understand that it’s a design philosophy to minimize clustering on a mobile web app, I believe that most designers underestimated the number of users who would want access to slightly more advance functionalities, and more careful design choices could have been made to allow access to them without severely compromising the app experience for the average users.

Main point 3: No automatic syncing

As mentioned by the presenter, Mendeley requires one to manually synchronize the data to the cloud.

With the proliferation of cloud apps like Dropbox and Google Drive, automatic synchronization has become a user expectation and such shortcoming would certainly frustrate and drive away users.

In the rapidly evolving world we live in today, it is important to keep up with the technology trends and design our applications with modern standards that meets the expectation of our users.

My thoughts on the Application Seminar

Computing students have a common conception that there are 10 things one can do in life:

1. Writing code
2. Wasting time

CS3216 is meant to be a module where one is not limited by the rigid curriculum of a typical module and is instead given a lot of freedom to learn by themselves and develop innovative apps on their own.

However, in this assignment, we were dragged away from our programming safe haven and forced to engage in mind-numbing task of making and preparing for Powerpoint presentation.

I understand that the intention behind this assignment is to provide an opportunity for us to systematically evaluate a successful application, so that we are able to learn what makes it successful and avoid its pitfalls.

Nonetheless, I believe we have already been subconsciously doing that when we use various applications in our daily lives. Therefore, the experience and knowledge gained might not match up to the time and effort invested on this assignment.

Nevertheless, to say that this assignment is a complete waste of time is neither politically correct nor fair to the assignment creator. As an optimistic opportunist, I always make the best of every experience I go through, regardless of whether it is forced upon me or that I willingly partake in it. I have certainly have picked up invaluable skills and improve my non-technical skills through this learning experience.

However, life is all about making trade-offs. In this increasingly competitive world, it is not sufficient to be learning. One must learn efficiently and effectively, utilizing every single second of the very limited time we have in this world to its maximum possible potential. Consequently, it is not easy to justify that this assignment is the worth the opportunity cost.

Lecture

Dr Damith conducted first part of the lecture, which touches on presentation skills. Unlike typical lectures of this nature (eg. those in CS2101), I find the lecture quite interesting as there were many useful pointers that are logically sound and yet commonly overlooked.

The second part of the lecture is conducted by Su Yuen, who touches on UI/UX design and brought our attention to pre-designed website templates that we can purchase to accelerate the development process.

This reminds me of a classic literature by Frederick Brooks: The Mythical Man-Month. Brooks postulate that the very nature of software precludes any invention that could have a multi-fold improvement in software development productivity, and therefore there is no silver bullet, which culminated at the famous quote:

The best way to attack the essence of building software is not to build it at all.

Like Su Yuen, Brooks also asserted that software should be purchased off the shelf whenever possible.

Yet, despite the advice from these brilliant individuals, I remain unease about using pre-designed website templates.

First, we have to spend considerable effort in searching for and evaluating a suitable template that is inline with our design style and technology stack (eg. React + Redux).

Next, we have to become familiarized with the code base and either adopt the code organization and style of the existing code base or adjust it to our liking.

As web development is a rapidly evolving field, it would be hard to find a well written code base that satisfies the requirements and employs the latest web technologies (eg. ES6). In the end, we might end up having to modify so much of the code base that we might as well have written it from scratch.

Yet, the very nature of software, in which a copy can be provided to another user at no marginal cost, compels one to believe that there are certainly scenarios in which developing on top of a purchased software can be more efficient than building it from scratch. Nonetheless, we should not rush into purchasing website templates and careful consideration has to be made with regards to the suitability of the chosen template for the task at hand.

Assignment 1 tidbit

When a component view is shared on Facebook, an URL link must be created that can be referenced back to the component view.

As some of our component views to be shared are collapsible panels and modals, it is slightly unconventional to control their state (collapsed, opened) through URL routing.

In the end, we manage to resolve it rather elegantly with React Router without compromising on performance or the Redux principle of single source of truth through  components such as Switch as well as push etc from the react-router-redux bindings.

Lectures

The first and second lecture for CS3216 have concluded. Admittedly, I have learnt nothing new from these 2 lectures.

For the software engineering related content, they have mostly been covered by CS2103 and CS3217, as well as through self reading and past SE experiences of our own.

As for the motivational speeches, I believe most people in this class are already sufficiently self-motivated for these speeches to have any further effects.

Lastly, for the course administration, the course website has already documented them clearly, therefore regurgitating them during lecture is not an efficient use of time.

All in all, the lecture content can be more concise and the lecture duration can be shortened.

Assignment 1 Idea

My group has embarked on our assignment 1 and I have finalised my project grouping for assignment 2.

The first stage of the project is ideation. We each did our research separately and came out with 2-3 ideas individually. Next, we met and discussed on the merits and potential pitfalls of every proposed idea. Lastly, we voted to identify the top 4 most popular ideas. We then concluded our first meeting so that we can have time to think about the 4 ideas and perform further researches on them. We met again the next day and every member was given a chance to voice their opinions and concerns about each of the 4 ideas, and after a lengthy discussion, we unanimously agreed to work on one of the idea.

The idea we have chosen is NUSEats, an app that compiles real time information about canteens and restaurants around NUS, such as opening hours, available dishes and crowdedness. The app will also incorporate social features such as broadcasting one’s intended lunch location, calling out/inviting friends to join for lunch etc. Other features of the app include opinion polls such as best modules to take, best lecturers etc.

Assignment 1 Team composition 

1. Jovin Liew: UI/UX designer/business person
2. Charlton Lim: Back-end developer
3. Ho Yihang: Full stack developer
4. Wang Riwu (me): Full stack developer

Assignment 1 Technology stack

For the front-end, we unanimously agreed to use React.js + Redux. The discussion on the merits of React and Redux shall be deferred to future posts when there are practical examples to demonstrate their apparent benefits.

For the back-end, even though Charlton is only experienced with Java/Spring Boot, he has agreed to learn Javascript/Express, as it has the advantage of enabling switching between server-side and client-side rendering (See also: Isomorphic Javascript) and is less verbose than Java (among various other advantages which I shall skip in this post as it might take a month to list them all).

For the database, Yihang initially suggested MongoDB but I recommended MySQL as RDBMS would allow clearer management of our data relationships, easier data normalisation to enforce data integrity and reduce data redundancy. In the end, we settled with PostgreSQL as Charlton is more familiar with it and there is not much practical difference between MySQL and PostgreSQL for our use cases.