8 ms·

Old, Good Database Design

182 points by jelnur 6y ago

gopalv 6y ago
> A well-thought design can save us many hours of coding, testing, and troubleshooting. That is the very definition of a waterfall design model. I've turned into a fluid-design advocate over the years, where every design principle follows a next question - "okay, this is good but how would I change it?". So you start with a unique constraint and four months later, you find out that it is not actually unique (like "two patients with the same email, because the 2nd one is a newborn on day of birth"). Or you normalize a data-set only to find out that your 1:N relationship turns into a 2:N relationship from before/after dates (like "UK" goes from "EU" to "UK"). The lost-time work of a design is usually the "okay, we did it in a world where UK was in EU - but we undo it & here's how" notes. Having a plan to decommission the nuclear plant you're building is super useful and often more relevant than designing it for efficiency alone.
- silvestrov 6y ago
  When you need to add info to your database, do you refactor existing tables or do you add a key-value table. I've seen so many key-value tables that really needed to be refactored into proper normalized tables. It's just so tempting to stick extra info into KV when you don't know where the project will end up.
- nightski 6y ago
  Good design as advocated by the author is generally conducive to change and is saying pretty much the same thing as you are. It's a lot easier to take away unique constraints later on instead of adding them in. It's easier to de-normalize some data for performance than to normalize it later on. The list goes on. The reason the waterfall method received so much bad press is because of requirements gathering, not the software or data design phases. Requirements are hard to get right the first time and they also change over time. But I'd be surprised to find someone argue that good architecture and design is a bad thing (being defined as the ability to adapt to changes in requirements).
  - dragonwriter 6y ago
    > The reason the waterfall method received so much bad press is because of requirements gathering, not the software or data design phases. No, it was because all three were done wrong. Requirements gathering is the biggest problem, true. But even if requirements were both knowable and fixed, for most projects, big up front requirements gathering, design, and then implementation would have lots of waste in the lean sense of effort expended that spends time not delivering customer value. Now, that gets made worse with the rework created by the fact that requirements gathering without validation by use gets lots of stuff wrong and that the context is often evolving such that requirements will drift between gathering, design, and implementation in a waterfall project, so that lots of work is done which never delivers value and needs reworked before it can do so, but the problem exists even without that exacerbation.
- pjmorris 6y ago
  Are you presuming that a 'well-thought [out] design' means that all the thoughts, and the design, came only at first? I can see a well-thought out design either as the waterfall you presume, or as an accretion of design decisions made over time as in your 'fluid design.' Either way can save many hours of coding, testing, troubleshooting.
- hinkley 6y ago
  > "okay, this is good but how would I change it?" Otherwise known as Reversible Decisions. Any decision that can be undone easily does not require the level of scrutiny, the level of investment. Save that investment for the things you won't be able to change. When faced with an irreversible decision, it's helpful to develop stalling tactics. Everything from distracting people with other issues to finding a way to get a 'taste' of the change without committing to it. It also helps if you stay on top of release notes for tools you use, and competitors of those tools. New opportunities might arise to use someone else's work to solve your problem better.
- jacques_chester 6y ago
  It's easier to safely loosen constraints than to add them post facto.
- pmontra 6y ago
  Well-thought design saves time even in agile projects. Maybe it's a one day activity instead of three months of it and yet it makes a difference. Basically each activity (or whatever we call it) is a micro waterfall. Example from today: a developer came back from a week of vacation, listened to the stand up meeting this morning and pointed out that we misunderstood the purpose of a table he worked on time ago. Result: a few hours of last week's coding were useless and we spent a couple of hours together at redesigning the activity. The total impact should be of about one day.
- teh_klev 6y ago
  > That is the very definition of a waterfall design model. I think you're conflating two different things. I've spent the last two days thinking about the impact of adding five new tables to our database for a feature, two of them just lookups. I've thought about the short term benefits and the long term possible problems (for something that isn't even spec'd yet). This isn't "waterfall" this is just getting a design about right for the current circumstances. If during the design phase you're already asking "okay, this is good but how would I change it?" then you're already waterfalling your design. Being thoughtful about your design up front (waterfall as your claim) hopefully solves many a problem down the line when you've suddenly got 5900M+ rows of data in the wrong shape in your production database; because a production database of that size doesn't take kindly to being "agiled" around.
- jelnur 6y ago
  > That is the very definition of a waterfall design model. No it doesn't mean that. When you can rely on your data it's easies to code and test than when you have to account for every bad data combination. And remove the constraint is easier when you need it, than to add it later. Thanks for the feedback!
- dkersten 6y ago
  Agile needs design too. Agile does not mean you don't do any up front work or any design work, it just means you have agility to adapt to change as you progress. A well thought out design often helps you be more agile, not less.
whirlwin 6y ago
This is useful for most small apps or truly monolithic apps. ...But normalization in a distributed environment with 50+ apps? Really? You need to have some sort of duplication unless you want big bottlenecks, performance penalties, and hot headaches From the linked MS article: "Redundant data wastes disk space and creates maintenance problems" Made me laugh well
- flowerlad 6y ago
  > You need to have some sort of duplication unless you want big bottlenecks, performance penalties I think that's called a cache. You can still have solid database design at the core. Then add redis on top of it.
  - Raidion 6y ago
    It can be non cached data as well. Imagine a data that needs to be sorted by data that's owned by another another service. You need to replicate the data as you can't do a join+sort between the data coming from two apis effectively. You don't treat that data as a source of truth, but do use it some UI purposes.
- simonw 6y ago
  Duplication is OK provided there are strict, documented rules about which copy is the "point of truth". If the duplicates diverge you need to know how to fix them.
simonw 6y ago
My least favorite part of database design is the bit where you have to pick lengths for your char columns. Twenty years in and I'm still picking these pretty much by guessing. And when I guess wrong it causes really annoying problems further down the line. I love how SQLite doesn't make me do this - it just has a TEXT type which is always unlimited in length.
- true_religion 6y ago
  With postgress it doesn't matter. Chars are just varcars under the hood, so you should always use the latter.
- mpolun 6y ago
  Same with postgres. I never use specific lengths for text on postgres.
- brobinson 6y ago
  Postgres also has a TEXT type like this. Edit: I see mpolun left a similar comment here, but it looks like he has been mostly shadowbanned for about 8 years.
  - alextheparrot 6y ago
    Just for general interest, how can you see their comment or assert they have been shadowbanned?
    - codetrotter 6y ago
      > how can you see their comment Go to your profile and ensure you have showdead set to yes. > assert they have been shadowbanned See https://news.ycombinator.com/threads?id=mpolun https://news.ycombinator.com/threads?id=mpolun and check out how many of their comments are showing as dead in combination with the contents of said comments. (The step above about setting showdead to yes might be required before you follow said link in order to actually see the dead comments). Almost every single one of the dead comments is contributing to the conversation. This is indicative of a shadow ban. HN users would not be downvoting the vast majority of these comments I think.
throwaway894345 6y ago
If one of the purposes of relational databases is data modeling, I've always wondered why there aren't good semantics for sum types. The real world is full of them, but databases can't express them. When I bring this up, some people respond that this is the purpose of ORMs; however, this implies that we have an arbitrary bifurcation in which some of the processing happens efficiently in SQL and anything that depends on sum types has to get hoisted up and over a network to application code. Further, it allows for different clients to behave differently, possibly having different notions of what any given sum type's variants are (which leads invariably to data corruption). I really wish databases did better here, but maybe I'm missing something.
- alextheparrot 6y ago
  Can you give an example of real world data modeling where you want more expressive sum types over just using enums? Enums are technically a subclass of sum types, but even those are non-trivial to use at a data format level (Try evolving them in an on-the-wire message format like Avro or Protobuf).
  - jimktrains2 6y ago
    I think they may mean that the result set has elements of different types. For instance if you stored restaurants by genre but wanted a list of all restaurants, but retaining all of the unique fields, you currently need to generate the product type of the genres.
  - talaketu 6y ago
    How do you model "postal address"? Some postal addresses are PO Boxes, some are street addresses, etc. There are canonical representations of these different cases. Do we just shove it all in a string, and let the application perform domain validation?
    - cgh 6y ago
      Each type of postal address is a separate column. New postal address "types" would get new columns. This works particularly well when addresses can have both PO boxes as well as street addresses. This is actually more flexible than tagged unions/sum types, at least for this particular case.
60secz 6y ago
Haven't you seen ghostbusters? "Don't cross the streams. It would be bad." If your data model has lines crossing over, it's the first smell of a bad design. Seriously tho, 5 minutes of untangling would make that data model diagram 100x better.
- klysm 6y ago
  I wonder if there’s actually any significance to if your data model forms a planar graph
roywiggins 6y ago
> we should keep numeric data in “integer”, “numeric” fields I end up keeping numeric data as text when I'm ingesting an external data source that I don't trust not to change ID format on me. They're all numeric now, but the format could change, and the actual numeric value of the ID is not important at all.
- bcrosby95 6y ago
  Yes, be careful conflating numeric data with data that looks numerical. IDs fall into the latter for sure. Unless you have complete control over it, then it might always be numeric. E.g. back when I thought I was super smart, on one project I made the credit card cvv a number. Except they can start with 0. Whoops.
  - barnabask 6y ago
    Same here, but with US postal codes. Having grown up on the west coast I didn't realize some started with a zero until embarrassingly late in life.
    - roywiggins 6y ago
      The best rule of thumb I've seen for this is that unless incrementing a value makes sense, probably use a text field unless you have some other really good reason. So: auto-incremented IDs yes, arbitrary IDs that you don't control no, phone numbers no, SSNs/PINs/ZIP codes, also no.
commandlinefan 6y ago
Some people choose nosql alternatives because they've spent time analyzing the performance of a proper relational model and have determined that an RDBMS will generate too much overhead for their data load and consciously accept the tradeoffs involved in giving up automated referential integrity. Most people, though, choose nosql alternatives because they're too lazy to learn how to model data.
- SamuelAdams 6y ago
  I am forever grateful that I took a full semester of database design in my undergrad. This single skill has stood with me for my entire career so far and has enabled me to figure out the root cause of many production issues. Plus people really like it when you can answer ad-hoc questions like "what inspections are still open and when were they first opened". If y'all can understand Angular / React / Vue there's no reason to not learn databases.
  - throw345hn 6y ago
    Could you suggest resources (books, articles, videos, moocs or others) to learn good database design. I am picking up skills about sql but want to better understand and learn about databases. As someone who doesnt have that background, a lot of the times I am just googling for stuff and just trying out bits and pieces.
    - EvanAnderson 6y ago
      Apparently there's a 4th edition coming, but if you like the form-factor of a book-length text I'd throw a recommendation to "Database Design for Mere Mortals"[1]. I read the 1st edition from the late 90's, but I'd imagine it's still just as good. It approaches database modeling from a practical non-technical perspective, and I found it helped me learn data modeling in a software-agnostic manner, and later to influence how I talked to non-technical audiences about data modeling. I'm really glad to have found it early in my career. I will echo what others have said. Data modeling is a force-multiplier type of skill. Combine it with a reasonable understanding of SQL and you can return a lot of value very quickly. [1] https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0321884493 https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp...
geebee 6y ago
Nice link. Nothing controversial, but sometimes simplicity is controversial in our field. I've slowly come around to seeing proper database design as the most essential foundation of an IT system. I remember reading "your data will outlast your application", and I've been around as a developer long enough to have lived it. One big anti-pattern I've seen with ORMs is that developers who don't really think in terms of data and relationships use the ORM as a kind of object serialization usable only from the application. Rather than thinking of the database as something useful that could be queried and accessed outside the context of the application, they write objects out to various tables and then re-import and re-construct them once they're needed again in the app, often with dependencies that are in code or even yaml configuration files. The upshot is that you simply can't use the database as anything other than a persistence tier for an app. It really would be no different if they had simply given the objects and id and pickled them to disk. The resulting data store really is that inaccessible and meaningless outside the context of the app. As a result, if an analyst wants a report, they can't write SQL to get those reports, even though the persistence tier is, if perhaps in name only, a "relational database". And when the app goes away (as it inevitably does), they'll still want to know, say, how many beakers and test tubes were ordered by a lab tech who participated in 100 or more experiments per year with at least one faculty member from radiology between June and December in 2007. But because they don't really know SQL, they see no value to it, and they're honestly just kinda irritated that they don't have an object database, which is what they understand a database to be - a way to pickle and reimport objects. The application outlasts the developer, and the data outlasts the application. Yeah, if you're writing an app that will hold potentially useful data, definitely think about how this will be accessed outside the context of the app that perhaps inspired its creation, and think about how you'd get at the data if the application went away. That'll probably lead you to old, good database design. Now, I actually do think that you can create a decent relational database through an ORM. I saw this go sideways with Rails a bunch of times, but I think that's probably because Rails made it so easy to start developing that a lot of people new to application development skipped the design stage of the data backend. You can construct a pretty robust database with migrations rather than CREATE TABLE statements, as long as you stick to the basic principles - but the technology makes it relatively easy to do the opposite and never really get into the mindset of data.
- 6y ago
janvdberg 6y ago
Reminds me of this great Derek Sivers post: https://sive.rs/pg https://sive.rs/pg If your design is good, you need less code.
- Raidion 6y ago
  Problem is that any changes to the databases need to be done on a single point of failure for your application. Any change that goes sideways, you risk downtime. Counter this with code, where I can deploy code along side my existing code and make sure it functions as I want it. If something fails, I just remove that instance from the LB. You don't want to touch something that valuable during regular feature cycles. Databases should be altered rarely and with much apprehension and a well established backup/rollback plan. I'm not saying you can't use constraints and stuff, but they should be really really static concepts that aren't subject to change. Unlike code, you can't share validation conditions across tables, so where you could update all the validation logic in a service with a change in one place, you have to update many tables in the database. I would stay away from triggers entirely, and use a queue/stream system to process data async. This can be better prioritized when the DB is under heavy load.
- nemothekid 6y ago
  >If your design is good, "Draw the rest of the fucking owl" Good design is hard. There are arguments to be made for both, but the problem with "Old, Good Database Design" is when the design changes it either devolves into 1. Downtime trying to move X billion rows 2. Some ad-hoc K/V store on top of your RDBMS And most companies tend to opt for (2) rather than (1). It's no surprise that some systems just decide to choose (2) from the onset.
rapfaria 6y ago
Which software is used to make those diagrams? The ones I use don't have good arrows like that
tarkin2 6y ago
JSONB objects with SQL relations in Postgresql is my happy-medium between the joy of schema-less JSON and the reassurance of SQL relations.
- c17r 6y ago
  Would you explain a little more? I'm intrigued. Are you saying SQL relations between fields inside JSONB columns between tables? A field in the JSONB column has foreign key to another table? And you can do a join?
  - tarkin2 6y ago
    You can do both. You can make relations between jsonb fields. Or, as I do: have a jsonb blob in one column, and a normal sql field in another, and do the sql joins with that. So I have some data fields as sql columns, and some as jsonb properties.
TheRealSteve 6y ago
This seems contrary to what I have learned in my career as an application developer on data heavy platforms. Namely the first section that concludes: > Having stressed the importance of good database design... I'm not in agreement with the author's concept of good design. I don't want other "doors" to edit the data that bypass the application logic. That's the mess SQL enables for DBAs and scripts that think it's okay to change data without executing business logic. I think it's better to accept your data and application layers are coupled and plan to evolve and refactor them together rather than teach your database to defend itself from the evil business logic in applications. I also don't think this dictates relational DBs vs nosql. Your data model is probably relational but how you choose to store and access the data depends more on what use cases you are trying to enable.
- nine_k 6y ago
  When you see that SQL access can ruin data and make them invalid, it's usually a strong indicator of an inadequate DB design. (Maybe you did not have a chance to see a good one?)
  - TheRealSteve 6y ago
    The pure, normalized models weren't performant enough so you are correct - the design wasn't all roses in the SQL systems. We had largely flattened down some table groups to be able to get data fast enough. But when you hit that point you may have outgrown RDBMS I'm not saying you can't get one to run fast enough but the ROI starts to decline significantly.
slifin 6y ago
Most relational databases aren't relational enough For example in Drupal you can have a node table which is a foreign key relationship to many other custom tables In SQL I can't say get me all the nodes that have a start date without explicitly left joining to a potentially dynamic number of other tables using a higher level language to modify the query In Datomic or Datascript or Datahike or Datalevin or Crux this query would be trivial these are not nosql databases they're EAVT datalog engines, that in some cases also support SQL for compatibility
- petepete 6y ago
  You're judging all relational databases having looked at Drupal's approach? Ok...
mooneater 6y ago
Is the diagram a joke? That's total ERD spaghetti.
- jelnur 6y ago
  It's included only as a picture :)
jrms 6y ago
Hi, my apologies if it's a bit off topic, but I wonder if you have any advise or can point me in some direction on the way to becoming a DBA, or something similar enough? I've been working as and old school sysadmin for the last 17 years and counting. Even if I can do nowadays devops stuff, I always liked the DBA work, based on my experience as sysadmin for DB servers (mysql, oracle, and friends...), and I'm considering moving my career in that direction. So is there any "DBA certification" for which I could take a course and all that? Thanks!!
- hobs 6y ago
  You've hit on the major problem with DBA work, which is that you are touching some of the most expensive prod stuff and even if you have "that cert" many places just want to see that somewhere else trusted you as a DBA for that type of product. I managed to transition in a company in dire straits that had no other options, and then getting new DBA jobs was fairly easy.
  - jrms 6y ago
    Thanks for your feedback, and I pretty understand what you mean, I've worked with some DBA know as the "million dollars error guy". But I think that's a second step, and anyway we were maintaining the servers where those dbs were running... So we deserve some trust maybe?
markus_zhang 6y ago
I'm wondering what you guys think about columnar databases and wide tables. We use Vertica and from senior colleagues and even Vertica developers I got the impression that big wide tables are good because it eliminates the needs of join. Thus we don't use star schema and just wide tables. However I think data modelling is also about embedding proper business logic and it would be a lot more confusing if two unrelated columns are stuffed in the same table. For example let's say my event based game has a base for each player and he can do a lot of things in his base. If we use a wide table then we wilk see columns related to factory building side by side with pig feeding, and because each small feature has some unique columns, a lot of those columns are NULL simply because this event json doesn't even have th fields. I'm wondering if we should use Vertica for a transactional type table and then use say sql server for dwh and build more traditional data modelling. But this could be awfully wrong maybe...
- teej 6y ago
  A few comments based on lots and lots of experience: - Wide tables in columnar DBs can make some analytics queries easier to write and sometimes more performant. - Wide tables can come at high storage cost and make other queries less performant (like SELECT *) - How much of these things happen is extremely DB dependent. How does the DB's underlying storage mechanism work? How is the data partitioned and distributed? How sophisticated and storage-aware is the query planner? How large is your data? How fast is your data growing? How fast do you need a new data point to be reflected in your dashboard? There's no free lunch when it comes to this stuff. A perfect solution doesn't exist, but the technology is getting better all the time. I've personally never used sql server as a data warehouse but plenty of folks do. The stuff I use that I recommend very highly - Snowflake, TimescaleDB, vanilla Postgres. Also, use dbt.
  - markus_zhang 6y ago
    Thanks teej for the answer. >How much of these things happen is extremely DB dependent. How does the DB's underlying storage mechanism work? How is the data partitioned and distributed? How sophisticated and storage-aware is the query planner? How large is your data? How fast is your data growing? How fast do you need a new data point to be reflected in your dashboard? I think most of my frustration comes from not knowing these stuffs. I work as a BA-BI hybrid as I found my data analysis skills are good complements to data modelling/airflow type of work, so I persuaded my manager to give me some BI work. But that also means I don't have the technical knowledge such as DB internals (and TBH I can't even find a book for Vertica on that matter). Our DB and DBAs and all ETL processes are located in HQ and we actually don't own our databases. This, I guess, adds an extra layer of discomfirt as we are effectively cut off from database-level optimization. Our data engineer is about to leave because he has no DE work to do (every ETL has to go through HQ's process and we only need to submit some configuration files). We don't have access to the databases you recommended (again HQ controls that), but I do believe I could try DBT, may I ask how do you use it? From my understanding it is mostly a transformation tool, but what makes it stand out?
unnouinceput 6y ago
When creating a visualization for your database design DO NOT under any circumstance cross relations as they go from one table to another. The client will get confused and frustrated. Looking at this article's visualizations hurt my eyes. Very bad choice for what's suppose to be a "good" design.
- jelnur 6y ago
  Thanks for feedback. I just included it as a picture, but you're right.

182 points by jelnur 6y ago

> A well-thought design can save us many hours of coding, testing, and troubleshooting. That is the very definition of a waterfall design model. I've turned into a fluid-design advocate over the years, where every design principle follows a next question - "okay, this is good but how would I change it?". So you start with a unique constraint and four months later, you find out that it is not actually unique (like "two patients with the same email, because the 2nd one is a newborn on day of birth"). Or you normalize a data-set only to find out that your 1:N relationship turns into a 2:N relationship from before/after dates (like "UK" goes from "EU" to "UK"). The lost-time work of a design is usually the "okay, we did it in a world where UK was in EU - but we undo it & here's how" notes. Having a plan to decommission the nuclear plant you're building is super useful and often more relevant than designing it for efficiency alone.

silvestrov 6y ago
When you need to add info to your database, do you refactor existing tables or do you add a key-value table. I've seen so many key-value tables that really needed to be refactored into proper normalized tables. It's just so tempting to stick extra info into KV when you don't know where the project will end up.
nightski 6y ago
Good design as advocated by the author is generally conducive to change and is saying pretty much the same thing as you are. It's a lot easier to take away unique constraints later on instead of adding them in. It's easier to de-normalize some data for performance than to normalize it later on. The list goes on. The reason the waterfall method received so much bad press is because of requirements gathering, not the software or data design phases. Requirements are hard to get right the first time and they also change over time. But I'd be surprised to find someone argue that good architecture and design is a bad thing (being defined as the ability to adapt to changes in requirements).
- dragonwriter 6y ago
  > The reason the waterfall method received so much bad press is because of requirements gathering, not the software or data design phases. No, it was because all three were done wrong. Requirements gathering is the biggest problem, true. But even if requirements were both knowable and fixed, for most projects, big up front requirements gathering, design, and then implementation would have lots of waste in the lean sense of effort expended that spends time not delivering customer value. Now, that gets made worse with the rework created by the fact that requirements gathering without validation by use gets lots of stuff wrong and that the context is often evolving such that requirements will drift between gathering, design, and implementation in a waterfall project, so that lots of work is done which never delivers value and needs reworked before it can do so, but the problem exists even without that exacerbation.
pjmorris 6y ago
Are you presuming that a 'well-thought [out] design' means that all the thoughts, and the design, came only at first? I can see a well-thought out design either as the waterfall you presume, or as an accretion of design decisions made over time as in your 'fluid design.' Either way can save many hours of coding, testing, troubleshooting.
hinkley 6y ago
> "okay, this is good but how would I change it?" Otherwise known as Reversible Decisions. Any decision that can be undone easily does not require the level of scrutiny, the level of investment. Save that investment for the things you won't be able to change. When faced with an irreversible decision, it's helpful to develop stalling tactics. Everything from distracting people with other issues to finding a way to get a 'taste' of the change without committing to it. It also helps if you stay on top of release notes for tools you use, and competitors of those tools. New opportunities might arise to use someone else's work to solve your problem better.
jacques_chester 6y ago
It's easier to safely loosen constraints than to add them post facto.
pmontra 6y ago
Well-thought design saves time even in agile projects. Maybe it's a one day activity instead of three months of it and yet it makes a difference. Basically each activity (or whatever we call it) is a micro waterfall. Example from today: a developer came back from a week of vacation, listened to the stand up meeting this morning and pointed out that we misunderstood the purpose of a table he worked on time ago. Result: a few hours of last week's coding were useless and we spent a couple of hours together at redesigning the activity. The total impact should be of about one day.
teh_klev 6y ago
> That is the very definition of a waterfall design model. I think you're conflating two different things. I've spent the last two days thinking about the impact of adding five new tables to our database for a feature, two of them just lookups. I've thought about the short term benefits and the long term possible problems (for something that isn't even spec'd yet). This isn't "waterfall" this is just getting a design about right for the current circumstances. If during the design phase you're already asking "okay, this is good but how would I change it?" then you're already waterfalling your design. Being thoughtful about your design up front (waterfall as your claim) hopefully solves many a problem down the line when you've suddenly got 5900M+ rows of data in the wrong shape in your production database; because a production database of that size doesn't take kindly to being "agiled" around.
jelnur 6y ago
> That is the very definition of a waterfall design model. No it doesn't mean that. When you can rely on your data it's easies to code and test than when you have to account for every bad data combination. And remove the constraint is easier when you need it, than to add it later. Thanks for the feedback!
dkersten 6y ago
Agile needs design too. Agile does not mean you don't do any up front work or any design work, it just means you have agility to adapt to change as you progress. A well thought out design often helps you be more agile, not less.

whirlwin 6y ago

This is useful for most small apps or truly monolithic apps. ...But normalization in a distributed environment with 50+ apps? Really? You need to have some sort of duplication unless you want big bottlenecks, performance penalties, and hot headaches From the linked MS article: "Redundant data wastes disk space and creates maintenance problems" Made me laugh well

flowerlad 6y ago
> You need to have some sort of duplication unless you want big bottlenecks, performance penalties I think that's called a cache. You can still have solid database design at the core. Then add redis on top of it.
- Raidion 6y ago
  It can be non cached data as well. Imagine a data that needs to be sorted by data that's owned by another another service. You need to replicate the data as you can't do a join+sort between the data coming from two apis effectively. You don't treat that data as a source of truth, but do use it some UI purposes.
simonw 6y ago
Duplication is OK provided there are strict, documented rules about which copy is the "point of truth". If the duplicates diverge you need to know how to fix them.

simonw 6y ago

My least favorite part of database design is the bit where you have to pick lengths for your char columns. Twenty years in and I'm still picking these pretty much by guessing. And when I guess wrong it causes really annoying problems further down the line. I love how SQLite doesn't make me do this - it just has a TEXT type which is always unlimited in length.

true_religion 6y ago
With postgress it doesn't matter. Chars are just varcars under the hood, so you should always use the latter.
mpolun 6y ago
Same with postgres. I never use specific lengths for text on postgres.
brobinson 6y ago
Postgres also has a TEXT type like this. Edit: I see mpolun left a similar comment here, but it looks like he has been mostly shadowbanned for about 8 years.
- alextheparrot 6y ago
  Just for general interest, how can you see their comment or assert they have been shadowbanned?
  - codetrotter 6y ago
    > how can you see their comment Go to your profile and ensure you have showdead set to yes. > assert they have been shadowbanned See https://news.ycombinator.com/threads?id=mpolun https://news.ycombinator.com/threads?id=mpolun and check out how many of their comments are showing as dead in combination with the contents of said comments. (The step above about setting showdead to yes might be required before you follow said link in order to actually see the dead comments). Almost every single one of the dead comments is contributing to the conversation. This is indicative of a shadow ban. HN users would not be downvoting the vast majority of these comments I think.

throwaway894345 6y ago

If one of the purposes of relational databases is data modeling, I've always wondered why there aren't good semantics for sum types. The real world is full of them, but databases can't express them. When I bring this up, some people respond that this is the purpose of ORMs; however, this implies that we have an arbitrary bifurcation in which some of the processing happens efficiently in SQL and anything that depends on sum types has to get hoisted up and over a network to application code. Further, it allows for different clients to behave differently, possibly having different notions of what any given sum type's variants are (which leads invariably to data corruption). I really wish databases did better here, but maybe I'm missing something.

alextheparrot 6y ago
Can you give an example of real world data modeling where you want more expressive sum types over just using enums? Enums are technically a subclass of sum types, but even those are non-trivial to use at a data format level (Try evolving them in an on-the-wire message format like Avro or Protobuf).
- jimktrains2 6y ago
  I think they may mean that the result set has elements of different types. For instance if you stored restaurants by genre but wanted a list of all restaurants, but retaining all of the unique fields, you currently need to generate the product type of the genres.
- talaketu 6y ago
  How do you model "postal address"? Some postal addresses are PO Boxes, some are street addresses, etc. There are canonical representations of these different cases. Do we just shove it all in a string, and let the application perform domain validation?
  - cgh 6y ago
    Each type of postal address is a separate column. New postal address "types" would get new columns. This works particularly well when addresses can have both PO boxes as well as street addresses. This is actually more flexible than tagged unions/sum types, at least for this particular case.

60secz 6y ago

Haven't you seen ghostbusters? "Don't cross the streams. It would be bad." If your data model has lines crossing over, it's the first smell of a bad design. Seriously tho, 5 minutes of untangling would make that data model diagram 100x better.

klysm 6y ago
I wonder if there’s actually any significance to if your data model forms a planar graph

roywiggins 6y ago

> we should keep numeric data in “integer”, “numeric” fields I end up keeping numeric data as text when I'm ingesting an external data source that I don't trust not to change ID format on me. They're all numeric now, but the format could change, and the actual numeric value of the ID is not important at all.

bcrosby95 6y ago
Yes, be careful conflating numeric data with data that looks numerical. IDs fall into the latter for sure. Unless you have complete control over it, then it might always be numeric. E.g. back when I thought I was super smart, on one project I made the credit card cvv a number. Except they can start with 0. Whoops.
- barnabask 6y ago
  Same here, but with US postal codes. Having grown up on the west coast I didn't realize some started with a zero until embarrassingly late in life.
  - roywiggins 6y ago
    The best rule of thumb I've seen for this is that unless incrementing a value makes sense, probably use a text field unless you have some other really good reason. So: auto-incremented IDs yes, arbitrary IDs that you don't control no, phone numbers no, SSNs/PINs/ZIP codes, also no.

commandlinefan 6y ago

Some people choose nosql alternatives because they've spent time analyzing the performance of a proper relational model and have determined that an RDBMS will generate too much overhead for their data load and consciously accept the tradeoffs involved in giving up automated referential integrity. Most people, though, choose nosql alternatives because they're too lazy to learn how to model data.

SamuelAdams 6y ago
I am forever grateful that I took a full semester of database design in my undergrad. This single skill has stood with me for my entire career so far and has enabled me to figure out the root cause of many production issues. Plus people really like it when you can answer ad-hoc questions like "what inspections are still open and when were they first opened". If y'all can understand Angular / React / Vue there's no reason to not learn databases.
- throw345hn 6y ago
  Could you suggest resources (books, articles, videos, moocs or others) to learn good database design. I am picking up skills about sql but want to better understand and learn about databases. As someone who doesnt have that background, a lot of the times I am just googling for stuff and just trying out bits and pieces.
  - EvanAnderson 6y ago
    Apparently there's a 4th edition coming, but if you like the form-factor of a book-length text I'd throw a recommendation to "Database Design for Mere Mortals"[1]. I read the 1st edition from the late 90's, but I'd imagine it's still just as good. It approaches database modeling from a practical non-technical perspective, and I found it helped me learn data modeling in a software-agnostic manner, and later to influence how I talked to non-technical audiences about data modeling. I'm really glad to have found it early in my career. I will echo what others have said. Data modeling is a force-multiplier type of skill. Combine it with a reasonable understanding of SQL and you can return a lot of value very quickly. [1] https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0321884493 https://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp...

geebee 6y ago

Nice link. Nothing controversial, but sometimes simplicity is controversial in our field. I've slowly come around to seeing proper database design as the most essential foundation of an IT system. I remember reading "your data will outlast your application", and I've been around as a developer long enough to have lived it. One big anti-pattern I've seen with ORMs is that developers who don't really think in terms of data and relationships use the ORM as a kind of object serialization usable only from the application. Rather than thinking of the database as something useful that could be queried and accessed outside the context of the application, they write objects out to various tables and then re-import and re-construct them once they're needed again in the app, often with dependencies that are in code or even yaml configuration files. The upshot is that you simply can't use the database as anything other than a persistence tier for an app. It really would be no different if they had simply given the objects and id and pickled them to disk. The resulting data store really is that inaccessible and meaningless outside the context of the app. As a result, if an analyst wants a report, they can't write SQL to get those reports, even though the persistence tier is, if perhaps in name only, a "relational database". And when the app goes away (as it inevitably does), they'll still want to know, say, how many beakers and test tubes were ordered by a lab tech who participated in 100 or more experiments per year with at least one faculty member from radiology between June and December in 2007. But because they don't really know SQL, they see no value to it, and they're honestly just kinda irritated that they don't have an object database, which is what they understand a database to be - a way to pickle and reimport objects. The application outlasts the developer, and the data outlasts the application. Yeah, if you're writing an app that will hold potentially useful data, definitely think about how this will be accessed outside the context of the app that perhaps inspired its creation, and think about how you'd get at the data if the application went away. That'll probably lead you to old, good database design. Now, I actually do think that you can create a decent relational database through an ORM. I saw this go sideways with Rails a bunch of times, but I think that's probably because Rails made it so easy to start developing that a lot of people new to application development skipped the design stage of the data backend. You can construct a pretty robust database with migrations rather than CREATE TABLE statements, as long as you stick to the basic principles - but the technology makes it relatively easy to do the opposite and never really get into the mindset of data.

6y ago

janvdberg 6y ago

Reminds me of this great Derek Sivers post: https://sive.rs/pg https://sive.rs/pg If your design is good, you need less code.

Raidion 6y ago
Problem is that any changes to the databases need to be done on a single point of failure for your application. Any change that goes sideways, you risk downtime. Counter this with code, where I can deploy code along side my existing code and make sure it functions as I want it. If something fails, I just remove that instance from the LB. You don't want to touch something that valuable during regular feature cycles. Databases should be altered rarely and with much apprehension and a well established backup/rollback plan. I'm not saying you can't use constraints and stuff, but they should be really really static concepts that aren't subject to change. Unlike code, you can't share validation conditions across tables, so where you could update all the validation logic in a service with a change in one place, you have to update many tables in the database. I would stay away from triggers entirely, and use a queue/stream system to process data async. This can be better prioritized when the DB is under heavy load.
nemothekid 6y ago
>If your design is good, "Draw the rest of the fucking owl" Good design is hard. There are arguments to be made for both, but the problem with "Old, Good Database Design" is when the design changes it either devolves into 1. Downtime trying to move X billion rows 2. Some ad-hoc K/V store on top of your RDBMS And most companies tend to opt for (2) rather than (1). It's no surprise that some systems just decide to choose (2) from the onset.

rapfaria 6y ago

Which software is used to make those diagrams? The ones I use don't have good arrows like that

tarkin2 6y ago

JSONB objects with SQL relations in Postgresql is my happy-medium between the joy of schema-less JSON and the reassurance of SQL relations.

c17r 6y ago
Would you explain a little more? I'm intrigued. Are you saying SQL relations between fields inside JSONB columns between tables? A field in the JSONB column has foreign key to another table? And you can do a join?
- tarkin2 6y ago
  You can do both. You can make relations between jsonb fields. Or, as I do: have a jsonb blob in one column, and a normal sql field in another, and do the sql joins with that. So I have some data fields as sql columns, and some as jsonb properties.

TheRealSteve 6y ago

This seems contrary to what I have learned in my career as an application developer on data heavy platforms. Namely the first section that concludes: > Having stressed the importance of good database design... I'm not in agreement with the author's concept of good design. I don't want other "doors" to edit the data that bypass the application logic. That's the mess SQL enables for DBAs and scripts that think it's okay to change data without executing business logic. I think it's better to accept your data and application layers are coupled and plan to evolve and refactor them together rather than teach your database to defend itself from the evil business logic in applications. I also don't think this dictates relational DBs vs nosql. Your data model is probably relational but how you choose to store and access the data depends more on what use cases you are trying to enable.

nine_k 6y ago
When you see that SQL access can ruin data and make them invalid, it's usually a strong indicator of an inadequate DB design. (Maybe you did not have a chance to see a good one?)
- TheRealSteve 6y ago
  The pure, normalized models weren't performant enough so you are correct - the design wasn't all roses in the SQL systems. We had largely flattened down some table groups to be able to get data fast enough. But when you hit that point you may have outgrown RDBMS I'm not saying you can't get one to run fast enough but the ROI starts to decline significantly.

slifin 6y ago

Most relational databases aren't relational enough For example in Drupal you can have a node table which is a foreign key relationship to many other custom tables In SQL I can't say get me all the nodes that have a start date without explicitly left joining to a potentially dynamic number of other tables using a higher level language to modify the query In Datomic or Datascript or Datahike or Datalevin or Crux this query would be trivial these are not nosql databases they're EAVT datalog engines, that in some cases also support SQL for compatibility

petepete 6y ago
You're judging all relational databases having looked at Drupal's approach? Ok...

mooneater 6y ago

Is the diagram a joke? That's total ERD spaghetti.

jelnur 6y ago
It's included only as a picture :)

jrms 6y ago

Hi, my apologies if it's a bit off topic, but I wonder if you have any advise or can point me in some direction on the way to becoming a DBA, or something similar enough? I've been working as and old school sysadmin for the last 17 years and counting. Even if I can do nowadays devops stuff, I always liked the DBA work, based on my experience as sysadmin for DB servers (mysql, oracle, and friends...), and I'm considering moving my career in that direction. So is there any "DBA certification" for which I could take a course and all that? Thanks!!

hobs 6y ago
You've hit on the major problem with DBA work, which is that you are touching some of the most expensive prod stuff and even if you have "that cert" many places just want to see that somewhere else trusted you as a DBA for that type of product. I managed to transition in a company in dire straits that had no other options, and then getting new DBA jobs was fairly easy.
- jrms 6y ago
  Thanks for your feedback, and I pretty understand what you mean, I've worked with some DBA know as the "million dollars error guy". But I think that's a second step, and anyway we were maintaining the servers where those dbs were running... So we deserve some trust maybe?

markus_zhang 6y ago

I'm wondering what you guys think about columnar databases and wide tables. We use Vertica and from senior colleagues and even Vertica developers I got the impression that big wide tables are good because it eliminates the needs of join. Thus we don't use star schema and just wide tables. However I think data modelling is also about embedding proper business logic and it would be a lot more confusing if two unrelated columns are stuffed in the same table. For example let's say my event based game has a base for each player and he can do a lot of things in his base. If we use a wide table then we wilk see columns related to factory building side by side with pig feeding, and because each small feature has some unique columns, a lot of those columns are NULL simply because this event json doesn't even have th fields. I'm wondering if we should use Vertica for a transactional type table and then use say sql server for dwh and build more traditional data modelling. But this could be awfully wrong maybe...

teej 6y ago
A few comments based on lots and lots of experience: - Wide tables in columnar DBs can make some analytics queries easier to write and sometimes more performant. - Wide tables can come at high storage cost and make other queries less performant (like SELECT *) - How much of these things happen is extremely DB dependent. How does the DB's underlying storage mechanism work? How is the data partitioned and distributed? How sophisticated and storage-aware is the query planner? How large is your data? How fast is your data growing? How fast do you need a new data point to be reflected in your dashboard? There's no free lunch when it comes to this stuff. A perfect solution doesn't exist, but the technology is getting better all the time. I've personally never used sql server as a data warehouse but plenty of folks do. The stuff I use that I recommend very highly - Snowflake, TimescaleDB, vanilla Postgres. Also, use dbt.
- markus_zhang 6y ago
  Thanks teej for the answer. >How much of these things happen is extremely DB dependent. How does the DB's underlying storage mechanism work? How is the data partitioned and distributed? How sophisticated and storage-aware is the query planner? How large is your data? How fast is your data growing? How fast do you need a new data point to be reflected in your dashboard? I think most of my frustration comes from not knowing these stuffs. I work as a BA-BI hybrid as I found my data analysis skills are good complements to data modelling/airflow type of work, so I persuaded my manager to give me some BI work. But that also means I don't have the technical knowledge such as DB internals (and TBH I can't even find a book for Vertica on that matter). Our DB and DBAs and all ETL processes are located in HQ and we actually don't own our databases. This, I guess, adds an extra layer of discomfirt as we are effectively cut off from database-level optimization. Our data engineer is about to leave because he has no DE work to do (every ETL has to go through HQ's process and we only need to submit some configuration files). We don't have access to the databases you recommended (again HQ controls that), but I do believe I could try DBT, may I ask how do you use it? From my understanding it is mostly a transformation tool, but what makes it stand out?

unnouinceput 6y ago

When creating a visualization for your database design DO NOT under any circumstance cross relations as they go from one table to another. The client will get confused and frustrated. Looking at this article's visualizations hurt my eyes. Very bad choice for what's suppose to be a "good" design.

jelnur 6y ago
Thanks for feedback. I just included it as a picture, but you're right.