Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Old, Good Database Design

relinx.io

Post reply on HN
Print view View original

121–130 of 167 posts

Re: Old, Good Database Design

#121

Post by barnabask »

bcrosby95 wrote: post #28
roywiggins wrote: post #25

> 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.

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.

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.

Re: Old, Good Database Design

#122

Post by sixdimensional »

markus_zhang wrote: post #80

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…

No. Vertica excels at being a column store database - a data model fit for analytics and OLAP. You should pretty much never use Vertica nor column stores for OLTP/transactional use cases.

It's not about the columns being wide, per se that is important in Vertica - although that is a benefit. It is about columnar storage and optimized querying, over row storage and optimized writing.

It would be the other way around - SQL Server or other transactional databases for that part. Actually, these days, SQL Server supports column store indexes on top of transactional tables- which gives you HTAP - hybrid transactional/analytical processing. A few other systems do this (Oracle too).

If you do HTAP, in a way you could even avoid using Vertica for data warehouse/OLAP use cases. Or you can build a separate dimensional data model in SQL Server and keep everything in there.

But if you already have Vertica, which is quite fast and good at OLAP queries on dimensional models, use it for that (enterprise data warehouse), and feed data from transactional systems in.

Re: Old, Good Database Design

#123

Post by chadcmulligan »

throw345hn wrote: post #45
SamuelAdams wrote: post #40

Earlier quoted context omitted.

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 re…

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.

If you can find a copy of this, I'd recommend it "Conceptual Schema and Relational Database Design: A Fact Oriented Approach" by Nijssen and Halpin. They presented a richer model (NIAM) for designing databases than the ER model by Codd/Date, though CJ Dates book is useful to. Both of these are out of print now, but anything by Halpin or Date would be a useful addition. To design databases relational theory and normal forms are the language you'd need to understand - they're not very hard though seems they aren't as widely taught any more.

Re: Old, Good Database Design

#124

Post by gfody »

throwaway894345 wrote: post #86
kccqzy wrote: post #67

Earlier quoted context omitted.

What's difficult about them? I typically use nullable columns and then a check constraint to specify a custom condition for nullability. Columns belonging to the same alternative in the sum type must be all null or all not null. And then there's check only one active alternative.

Type safety And query semantics, mostly. You can use constraints to get some of this back, but it only goes so far. Ultimately there’s a reason statically typed programming languages developed sum types, and all of those reasons apply to databases as well because data is data.

you can avoid nullable cols by using separate physical tables for each concrete type with surrogate PKs from a common sequence. these can be concatenated back together in a view (with no performance hit if you're careful not to hide indexes and predicate pushdowns). FKs then can reference the appropriate concrete table and in case you need an FK on some union you can use an indexed view instead.

I've used this approach for modeling entities that are polymorphic in object-land and haven't felt particularly underequipped - more robust indexed view support would be nice but that's active research territory (one of the hardest problems in computer science, materialize.io looks promising!)

Re: Old, Good Database Design

#125

Post by jayd16 »

lmm wrote: post #102
alextheparrot wrote: post #20

Earlier quoted context omitted.

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).

Imagine a system that allows third-party login (Facebook / AppleID / whatever) - then the account has either a username/password hash or an oauth token or some other kind of structured data. Delivery addresses for a system that supports both physical and digital products - you want a type-level distinction between physical and digital addresses, but an order might be being shipped to either. Subscription vs free tria…

>Imagine a system that allows third-party login (Facebook / AppleID / whatever) - then the account has either a username/password hash or an oauth token or some other kind of structured data.

What I've seen most often is you have to deal with account merging but lets say you do really want either/or...

Wouldn't you just have your third party tables (each with their own idiosyncrasies) and in your user table you'd have login_type and login_id columns? You know which table to hit by type using the id?

Re: Old, Good Database Design

#126

Post by TheRealSteve »

nine_k wrote: post #109
TheRealSteve wrote: post #54

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 oka…

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?)

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.

Re: Old, Good Database Design

#127

Post by FridgeSeal »

lmm wrote: post #116
FridgeSeal wrote: post #114

Earlier quoted context omitted.

Even if you have an ETL pipeline to an OLAP database/data warehouse/etc, if your core database design is hostile to the analytics/etc then it's going to be a pain no matter how carefully they use it. > it's really important to have a single owner for that database, or you'll never be able to evolve the schema... IMO, the "owning" application/developers reserve the right to evolve the schema-and if that temporarily br…

> Even if you have an ETL pipeline to an OLAP database/data warehouse/etc, if your core database design is hostile to the analytics/etc then it's going to be a pain no matter how carefully they use it. Disagree. You don't need a single "core database design". It's fine to have different representations of your data for different purposes, and a transformation pipeline between them; that's the whole idea of CQRS etc.

Yes I'm not disagreeing there, I'm all for pipelines and CQRS and dedicated databases for dedicated purposes. The point I'm making is that if the original schema is a pain to work with, you can have as many pipelines and databases as you want, getting the actual data you want isn't any less of a pain.

Re: Old, Good Database Design

#128

Post by Supermancho »

PaulStatezny wrote: post #99
Supermancho wrote: post #51

Earlier quoted context omitted.

> Nothing controversial ahem > Foreign Key constraint is the king of the relational database design Amazon does not use FK constraints and I have rarely run into systems that do (since 1996ish). Most people with big enough datasets learn not to use them. The overhead for orphaned data is far less than the consequences of using them.

Can you clarify about the overhead you're speaking of? I assume it only comes into play at super massive scale like Amazon-level datasets.

When not using FK constraints, data may be modified or lost (deleted) without proper cleanup of related records leading to "orphaned" records without relationships (or related ids that do not correspond to existing records). The disk space for these orphaned records and the time to run a relational integrity checks across tables (removing orphans), is of minor concern and effort, comparative to a troublesome database entry with FK constraints.

Re: Old, Good Database Design

#129

Post by c17r »

tarkin2 wrote: post #49

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

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?

Re: Old, Good Database Design

#130

Post by lmm »

FridgeSeal wrote: post #127
lmm wrote: post #116

Earlier quoted context omitted.

> Even if you have an ETL pipeline to an OLAP database/data warehouse/etc, if your core database design is hostile to the analytics/etc then it's going to be a pain no matter how carefully they use it. Disagree. You don't need a single "core database design". It's fine to have different representations of your data for different purposes, and a transformation pipeline between them; that's the whole idea of CQRS etc.

Yes I'm not disagreeing there, I'm all for pipelines and CQRS and dedicated databases for dedicated purposes. The point I'm making is that if the original schema is a pain to work with, you can have as many pipelines and databases as you want, getting the actual data you want isn't any less of a pain.

> if the original schema is a pain to work with, you can have as many pipelines and databases as you want, getting the actual data you want isn't any less of a pain.

I don't think that's really true. If the original schema is just something you're ingesting before transforming then it doesn't really matter how bad it is; all you're gonna be doing is scanning over all the tables one way or another.

Post reply on HN