Ciarán Ainsworth 
Does anyone have any recommendations for books about database design? I'm realising that I really don't understand the fundamentals very well, so I'm probably making a lot of mistakes that will bite me later on.
Amy Pillow@cda I'm curious what prompted that? I think the risk of an improper db schema is not like, potential data loss or the db becoming harder to maintain and expand, but rather that the backend codebase will continually accrue technical debt in order to work around poorly defined entities and relationships. If the schema causes friction with the backend API, it should be addressed at the database level as quickly as possible. But also, like, perfectly aligned db schemas don't exist in the wild, like they do in textbooks. We are all imperfect beings with deadlines. Are you designing something from scratch?
@amy I'm designing it from scratch, and as part of that I'm thinking about the fact that there are probably places where I'm going to need to divide data up because their state changes asynchronously. That means I need to have some fundamental understanding of the best way to structure those tables, how to index them, the best way to write queries, etc. etc.
I know how to do the actual breaking up of everything and how to create indices in my code, but I'm realizing that I don't understand database systems and design well enough to make smart decisions there, you know?
@cda you mentioned that the data might be time-dependent, if I understand what you were saying, and in my opinion, databases ideally shouldn't know where or when they are. Like, if a row should be marked as stale if it was updated more than a week ago, most relational dbs offer a way to schedule a task every night that could set the 'stale' column to true if necessary. But that's not how I would approach it. I would pass the current time in to a query against the last_updated column, so that the stale state of the row is a computed property rather than a time-dependent column. I think a space/time/env-independent schema is always possible to design. Like, I would like to be able to physically pick up the database and move it to a different timezone, send it 10 years in the past or future with my portal, whatever, and the stateless backend should be able to use it still.
Beyond that, I don't have much high level advice lol. If I'm being honest, I think I'm sensing a little impostor syndrome. I think you have more experience than you think you do, and you're asking the right questions, just try not to get too stuck in the research phase.
Beyond that, I don't have much high level advice lol. If I'm being honest, I think I'm sensing a little impostor syndrome. I think you have more experience than you think you do, and you're asking the right questions, just try not to get too stuck in the research phase.
@amy It's not really time-dependent, I probably didn't express that well. It's more like I suspect that parts of the record might need to be updated or expanded separately in future. It's about podcasts, and my suspicion is that if a podcast feed changes, we need a mechanism by which to resolve multiple feed URLs to the same feed entity, which means storing feed URLs separately and referencing the parent record.
So it's not time so much as it is wanting to have a central resource entity then splitting up any information I know to be variable into separate records. I just want to make sure I'm A) not overcomplicating the design and B) future-proofing it in a scaleable and extensible way. I know enough to realize there might be an issue there, but not enough to know how to design the structure properly, you know?
Salacious Crumb@cda I have a very similar, but very different challenge. Building an info security database I want to know where my IT systems are and (for example) how many vulnerabilities they have. This sounds easy on day one - have a table with columns for IP, Hostname, and vulnerability count. But those things change regularly. Next week it may change IP and have less vulnerabilities, and the following week, it will have more.
How do I store all of that so I can graph vulnerabilities over time?
@cda oh yes that makes a lot of sense now, it's really hard to change your mind later on about that. But, ok, I actually do have some words about design complexity. Because, knowing that a string might need to become an array of strings is honestly so valuable, even if you're slightly unsure. Imagine all the necessary complexities you haven't thought of or accounted for yet. A slight increase in the initial complexity of the design to accommodate a realistic use case is very worth it in my opinion. And there have been so many instances where I would say "I wish we thought about x earlier," so when you are lucky enough to foresee something, I would suggest you act on it. In the worst case you will have an extra JOIN in some queries for no good reason.
Michał "rysiek" Woźniak · 🇺🇦
elfenlaid@cda Please let me jump into this better database schema design train
I have a copy of https://theartofpostgresql.com and it's kinda nice. It has a chapter on data modeling, but I wish something that explores it deeper with more examples.
thomas_shone@cda possibly just to help narrow down what type of book you're looking for:
Based on the conversations here, you're probably looking specifically for data modelling knowledge. Specifically online transaction processing (OLTP) compared to online analytical processing (OLAP) modelling. I.E.: you're modelling for a running web application rather than for big data analytics.
It might be an idea to take a look at Database Normalisation (https://en.wikipedia.org/wiki/Database_normalization) and see if this seems close to what you're trying to learn about.
Otherwise you might get buried in books around performance or tweaking specific database systems which is another whole topic (query performance, index design, table engines, etc).