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

71–80 of 167 posts

Re: Old, Good Database Design

#71

Post by ticmasta »

EvanAnderson wrote: post #58
throw345hn wrote: post #45

Earlier quoted context omitted.

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.

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

Not required, but knowing a little relational algebra helps queries make a lot more sense. A lot of the features like constraints and indices are very thin veneer over the underlying concepts.

At the other end of the spectrum, once you can write a few basic queries check out something like SQL Murder Mystery https://mystery.knightlab.com/

This one is super-fun and lets you practice some basic to low-intermediate skills.

Re: Old, Good Database Design

#72

Post by johnthescott »

Cieplak wrote: post #66
commandlinefan wrote: post #27

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.

> have determined that an RDBMS will generate too much overhead I think the read/write overhead is mostly a function of schema design, rather than an intrinsic property of an RDBMS. Denormalized schemas have similar performance profiles to document-oriented storage. Moreover, mainstream SQL databases like Postgres are getting better and better at indexing jsonb fields, indexing time series data with BRIN, rudimentary…

for text search the rum index is quite robust. my guess is that rum (or something like it) will be introduced into the pg core soon. we index many terabytes of pdf files excellent performance.

   https://github.com/postgrespro/rum
also, postgrespro are behind the json/b indexing.

Re: Old, Good Database Design

#73

Post by ticmasta »

mcny wrote: post #60
throw345hn wrote: post #45

Earlier quoted context omitted.

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.

I'd like to know this as well. I think you'll just have to build things (potentially horribly) and fail. I took three semesters of database (granted, baby database classes) and I still have no idea how you can do something pretty straightforward like creating a room reservation system. If there is a reservation beginning at 10:15 AM and ending at 12:30 PM and someone tries to book a reservation from 10:00 AM to 10:30…

hold start & end time in normal DB columns, use business logic for your rule enforcement, have a query to identify rule violation. Maybe add constraints on your warehouse data but I probably wouldn't on the operational DB. I could be convinced to do so for the PoC because it might make sense, but what if you're managing thousands or millions of scheduled "resources" like a chain of libraries, or hotel rooms? What if you need to coordinate "leases"? DB-level is an OK place to start, but also one of the hardest to tease apart when you need to scale.

This is kind of the point of data modeling; you start with an idea, make it all pretty 3rd-normal form, define your projected indices and checks and constraints, then you mess it up a bit where it makes sense or you've had experience in the past.

YMMV

Re: Old, Good Database Design

#74

Post by cgh »

SamuelAdams wrote: post #40
commandlinefan wrote: post #27

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.

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…

Absolutely agree. Learning data normalisation was up there with Typing 9 (touch-typing class in grade 9, not Hindley-Milner whatever) in terms of long-term usefulness.

Re: Old, Good Database Design

#75

Post by rdiddly »

commandlinefan wrote: post #27

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.

All depends what you're doing, right? Is it a small stupid app with a short lifespan? Is it "just" a proof-of-concept? Is the company likely to survive long enough that technical debt matters? (oblig. HN/SV/startup comment) Is there a ridiculous imbalance between data ingestion volume vs. reads/retrieval? All good arguments for alternative/noSQL approaches.

Re: Old, Good Database Design

#76

Post by cgh »

talaketu wrote: post #70
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).

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?

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.

Re: Old, Good Database Design

#77

Post by fizixer »

ticmasta wrote: post #71
EvanAnderson wrote: post #58

Earlier quoted context omitted.

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

Not required, but knowing a little relational algebra helps queries make a lot more sense. A lot of the features like constraints and indices are very thin veneer over the underlying concepts. At the other end of the spectrum, once you can write a few basic queries check out something like SQL Murder Mystery https://mystery.knightlab.com/ This one is super-fun and lets you practice some basic to low-intermediate skil…

Fun fact: You can do relational algebra on plain text data (e.g., csv, as well as output of commands like ls, etc.) using only shell + piping + well-known shell tools.

Re: Old, Good Database Design

#78

Post by PhineasRex »

throwaway894345 wrote: post #16

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…

There's no real reason you can't have sum types in a relational database, this just goes back to our current crop of RDBMSs being quite old and predating the current emphasis on strong typing. I suspect a modern relational database with an easier way define complex types (not writing a plugin) would be very popular, but the amount of work required to make something like that is immense

Re: Old, Good Database Design

#79

Post by DaiPlusPlus »

kccqzy wrote: post #67
throwaway894345 wrote: post #16

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…

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.

This approach doesn’t scale when business requirements change regularly such that you need to add or remove columns to an existing table.

Adding new columns by creating a new table is easy and cheap and doesn’t involve downtime. Adding 100+ columns to an existing table because the spec said a relationship went from 1:0-1 to 1:1 is a pain.

(This can be avoided with creative design with deferrable constraints, something certain major RDBMS are still lacking, gah!)

Re: Old, Good Database Design

#80

Post by markus_zhang »

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

Post reply on HN