Relational databases aren’t dinosaurs, they’re sharks • Hacker News Board

Re: Relational databases aren’t dinosaurs, they’re sharks

#71

Post by mattbuilds » Sat, Oct 09, 2021, 1:04 PM UTC

jhgb wrote: post #60

squeaky-clean wrote: post #58

Earlier quoted context omitted.

I work with locations and addresses on an international system. They are not anywhere near as standardized as to allow this. And also at scale, you'll get things that seem like they shouldn't be addresses but are. One of our addresses that caused trouble is literally: "The yellow sign across the street from the Seven-Eleven at ". We have one address that's legally in two countries at the same time. One address is jus…

I understand these issues. That's precisely why a custom type seems appropriate -- it should be able to cover all the alternatives while not burdening you with the problem of storing a discriminated union as a set of disparate relations. Or, at the very least, if this approach still has some issues remaining, it should still have fewer issues than any other approach I can think of, because there's nothing you can't d…

This is one of those things that sounds good in theory and the reality gets in the way and is way too messy.

Re: Relational databases aren’t dinosaurs, they’re sharks

#72

Post by out_of_protocol » Sat, Oct 09, 2021, 1:07 PM UTC

makapuf wrote: post #46

kroolik wrote: post #28

Earlier quoted context omitted.

> Some VP will ask "what weekday do users comment the most". This is why people invented read models. Because the data model that works for the app and makes it easy to work with there might not necessarily work for analytics. Generate analytics data from you app model so that both world can move independently. > Need an admin portal? With blobs you are coding all these custom admin pages. Admin portal with direct ac…

or logic is in db, with validation triggers and constraints constraints, but that is not always the case.

Nah, database can and should check data consistency but not the business logic (otherwise you whole app is just sql triggers and stuff like that). Also, business logic tend to change over time, but existing items do not

Re: Relational databases aren’t dinosaurs, they’re sharks

#73

Post by jhgb » Sat, Oct 09, 2021, 1:08 PM UTC

avianlyric wrote: post #70

jhgb wrote: post #60

Earlier quoted context omitted.

I understand these issues. That's precisely why a custom type seems appropriate -- it should be able to cover all the alternatives while not burdening you with the problem of storing a discriminated union as a set of disparate relations. Or, at the very least, if this approach still has some issues remaining, it should still have fewer issues than any other approach I can think of, because there's nothing you can't d…

> I understand these issues. I seriously doubt you do. The closest you can get to “standard address format” is: Address Line 1 Address Line 2 Address Line n Postal code (which can be blank) Postal Area (which can be blank) Country There is no way to build “first-class” support for addresses. Because theres no such thing as a valid or invalid address, only whether or not someone can find the correct location by readin…

I don't see how this changes anything. You can accommodate any address format, or any finite union of multiple address formats, including any computed or materialized views of the address with a custom type (AND including an "I give up" default for when everything else fails).

> there’s no standard, it’s entirely dictated by local conventions, which can change street-to-street, city-to-city

Well, that may be an international issue. In my country's case, it's quite clearly defined by law. A type for an international case might by necessity be a union of unions.

Re: Relational databases aren’t dinosaurs, they’re sharks

#74

Post by MikeDelta » Sat, Oct 09, 2021, 1:10 PM UTC

lmilcin wrote: post #41

The choice of technology by developers and their managers is guided in large part by trends rather than by wholly rational decision process. The type of database should be dictated by the application requirements. Relational databases provide some exceptional guarantees while also being able to run quite large systems. This means, when you have: 1. Relational data, 2. Queries that are not known beforehand, 3. Data th…

True, I've also seen enterprises where central architects decide for what framework/solution the whole company should go.

The answer to questions like 'what is the best DB' is online always 'depends on your use case', but in enterprises is usually 'what upstairs decided'.

Re: Relational databases aren’t dinosaurs, they’re sharks

#75

Post by hug » Sat, Oct 09, 2021, 1:14 PM UTC

jhgb wrote: post #60

squeaky-clean wrote: post #58

Earlier quoted context omitted.

I work with locations and addresses on an international system. They are not anywhere near as standardized as to allow this. And also at scale, you'll get things that seem like they shouldn't be addresses but are. One of our addresses that caused trouble is literally: "The yellow sign across the street from the Seven-Eleven at ". We have one address that's legally in two countries at the same time. One address is jus…

I understand these issues. That's precisely why a custom type seems appropriate -- it should be able to cover all the alternatives while not burdening you with the problem of storing a discriminated union as a set of disparate relations. Or, at the very least, if this approach still has some issues remaining, it should still have fewer issues than any other approach I can think of, because there's nothing you can't d…

This sounds to me like a "just draw the rest of the owl" solution.

You're right in that there's nothing you can do with JSONB that you can't do with a custom type, but the larger problem is "do I even know how to make a custom type that might fit all of the various inconsistencies that I might face with this data?"

The answer, for me, is no. I have no idea, and I'm not going to pretend. I can throw it all into a giant chunk of JSON and leave explicit note that address handling isn't, and at least that way no one actually thinks that it is, which is probably safer for everyone involved.

Re: Relational databases aren’t dinosaurs, they’re sharks

#76

Post by ByteJockey » Sat, Oct 09, 2021, 1:19 PM UTC

jhgb wrote: post #66

vharuck wrote: post #65

Earlier quoted context omitted.

I've come around to this view, too. Also like names, the best solution is to avoid using them for analysis. Run them through an address geocoding service and store the coordinates next to the address. Use the original address for sending mail or filling out forms, and coordinates for analysis. Unless cleaning, parsing, and geocoding addresses is one of your core business values, let somebody else do it. It's a lot of…

My idea is not in contradiction with your idea. In fact your idea is a part of my idea.

Given that the person started their comment with:

> I've come around to this view, too.

I don't think they were disagreeing with you.

Re: Relational databases aren’t dinosaurs, they’re sharks

#77

Post by afandian » Sat, Oct 09, 2021, 1:20 PM UTC

vharuck wrote: post #65

squeaky-clean wrote: post #58

Earlier quoted context omitted.

I work with locations and addresses on an international system. They are not anywhere near as standardized as to allow this. And also at scale, you'll get things that seem like they shouldn't be addresses but are. One of our addresses that caused trouble is literally: "The yellow sign across the street from the Seven-Eleven at ". We have one address that's legally in two countries at the same time. One address is jus…

I've come around to this view, too. Also like names, the best solution is to avoid using them for analysis. Run them through an address geocoding service and store the coordinates next to the address. Use the original address for sending mail or filling out forms, and coordinates for analysis. Unless cleaning, parsing, and geocoding addresses is one of your core business values, let somebody else do it. It's a lot of…

This is why two successive Deliveroo drivers went to the wrong street this week. The first 'tried to deliver' and gave up, and I had to run after the replacement one.

Despite the address and postcode being both correct and unambiguous the driver followed a pin to a badly geocoded coordinate, and didn't even look at the street name.

I'd encourage anyone (especially Deliveroo) to make more effort to model addresses in the correct locale (UK in my case) rather than taking these shortcuts.

Re: Relational databases aren’t dinosaurs, they’re sharks

#78

Post by chousuke » Sat, Oct 09, 2021, 1:20 PM UTC

lmilcin wrote: post #41

The choice of technology by developers and their managers is guided in large part by trends rather than by wholly rational decision process. The type of database should be dictated by the application requirements. Relational databases provide some exceptional guarantees while also being able to run quite large systems. This means, when you have: 1. Relational data, 2. Queries that are not known beforehand, 3. Data th…

I default to choosing either SQLite or PostgreSQL until there's sufficient reason to think about using something else. I think it's pretty rare to build an application where you know you will have scalability issues with a relational model, but I can tell you with confidence that if you ever do need to make changes to how your data is stored, you will be happy if the source is a datastore that has enforced integrity guarantees. It's much easier to migrate data from one system to another when you don't first have to check that it's all valid in the first place.

Re: Relational databases aren’t dinosaurs, they’re sharks

#79

Post by jhgb » Sat, Oct 09, 2021, 1:22 PM UTC

ByteJockey wrote: post #76

jhgb wrote: post #66

Earlier quoted context omitted.

My idea is not in contradiction with your idea. In fact your idea is a part of my idea.

Given that the person started their comment with: > I've come around to this view, too. I don't think they were disagreeing with you.

Concurring with a disagreeing view is disagreeing, isn't it?

Re: Relational databases aren’t dinosaurs, they’re sharks

#80

Post by jhgb » Sat, Oct 09, 2021, 1:28 PM UTC

hug wrote: post #75

jhgb wrote: post #60

Earlier quoted context omitted.

I understand these issues. That's precisely why a custom type seems appropriate -- it should be able to cover all the alternatives while not burdening you with the problem of storing a discriminated union as a set of disparate relations. Or, at the very least, if this approach still has some issues remaining, it should still have fewer issues than any other approach I can think of, because there's nothing you can't d…

This sounds to me like a "just draw the rest of the owl" solution. You're right in that there's nothing you can do with JSONB that you can't do with a custom type, but the larger problem is "do I even know how to make a custom type that might fit all of the various inconsistencies that I might face with this data?" The answer, for me, is no. I have no idea, and I'm not going to pretend. I can throw it all into a gian…

In my case, I actually do know that; there's even a legal definition of what an address is and there's a national registry of all addresses. The problem is that I can't solve this for every country myself since I only have national knowledge (which is sufficient for my needs, fortunately).