So as I was scurrying around the internet for database architecture based questions as I usually do with my free time on a Saturday afternoon, a thought came across my mind, all these blog posts and articles point to what TO DO. But not any single one of them describe a what NOT to do.
Both MySQL and NoSQL. but please indicate as to which. Im certain each will have its own set of topics.
Im just wondering if anyone here can share their experiences.
Top comments (1)
Good to hear I'm not the only one spending their Saturday learning more about databases :)
While not strictly DB architecture here are some thoughts
Exhausting primary key space by using an int. You will then be in the impossible situation of having to take a scheduled outage to change this if your table grows to millions or billions of rows. Even if you don't get to this stage inserts will fail once you run out of keys. Use a bigint instead.
Not using staging tables when performing ETL. The bigger your database becomes the longer the table will be out of action if you are going to TRUNCATE or DROP it to update your table.
Creating an API that allows third parties to infer your growth by seeing your primary keys. Create an alternate key or mapping table instead.
Overnormalising your tables. Normal form is great, but don't go overboard or you'll be using too many JOINs and slowing down performance.
Not sharding your database and having to commit to more and more outages for any fixes as your database grows.
Consider your indexes and how the data will be queried. Not maintaining indexes, or alternatively, overindexing slows performance.