Database design in 2019
I’ve been working in this crazy world of programming and system administration for quite some time and this topic seems to come back and haunt me quite a bit so I decided to put my thoughts on paper…or on medium at least.
This xkcd comic really got me scratching my head on the topic of:”Access to Digital Resources over Time.”
This got me thinking what would happen if all good digital resources on the topic of database design were to disappear. Well then I guess this blogpost would get plenty of hits.
Designing a good relational database is still super relevant in 2019.
In 2016 I did this course which I found super helpful:
https://coursera.org/learn/python-databases?
This course has a 4.8/5 Rating on Coursera for a very good reason.
If you check the Syllabus of this course you will see there is small section on database design, if you wanted to just take one thing from this course then watch the videos related to database design and go through the examples:
Why was I looking at database design in the first place?
I’m trying to build a REST API for fun, my next natural step would be to build a frontend to interact with the REST API. For the sake of doing things right I decided to design my database right before doing anything else for my REST API. After all if I modelled the data correctly then it might have been much easier to create a REST API.
GRPC:
I’m not using GRPC or anything fancy to create this REST API, although this is something I’m super interested in doing. If you don’t know about GRPC yet I highly recommend looking into it at some point:
https://medium.com/namely-labs/how-we-build-grpc-services-at-namely-52a3ae9e7c35
Back to Database Design:
I was busy building my REST API when it occurred to me I didn’t really know how to model the database properly, which led me to watching the Coursera course from that Course again. I really wanted to design a database properly, but there was a great deal consider, one of those considerations is convention, it seems that convention governs quite a bit of database design.
Here are some tools I tried that might help you:
DB Designer:
This tool is pretty handy and has support for many types of databases.
I had to export the queries used to create the database and try run them in MySQL, I then just googled for the errors and fixed the errors to get the create statements to work:
MySQLWorkBench:
You probably have this tool installed on your computer and you probably no idea this tool can be used to make ERD diagrams:
I only know about this functionality thanks to a really cool open source project called:”OSSClass” open source classifieds. OSSClass gives a database diagram that can be downloaded and opened in MySQLWorkbench see here:
The pic is a screenshot from MySQLWorkbench. Alternatively you can reverse engineer a database that you already have on your computer:
My favourite ORM:
I know some people are gonna read till this heading and then stop reading. My problem with databases really started because I was using Django quite a bit for Development which made me get use to an ORM.
What is my favourite ORM?
My favourite ORM is called “No RM” . It is pronounced No ORM. Don’t look it up though. If you are gonna roll your own SQL make sure to read up on SQL injection and paramterized queries. I think the fact that many people use ORM’s actually protect’s them from SQL Injection, but this is due to general ignorance not due to ORM’s being good.
TLDR:
ORM’s are okay just don’t use them because of your lack of database knowledge try brush up on relational db basics this might even help you use your ORM better.
Database design is still a tough topic in 2019.
What do you use to design db’s?
