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

151–160 of 167 posts

Re: Old, Good Database Design

#151

Post by roywiggins »

barnabask wrote: post #121
bcrosby95 wrote: post #28

Earlier quoted context omitted.

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.

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.

Re: Old, Good Database Design

#152

Post by throwaway894345 »

gfody wrote: post #149
throwaway894345 wrote: post #142

Earlier quoted context omitted.

Normal form supports complex product types just fine. A row is an object and a row containing a reference to a row in another table is equivalent to an object containing another object, and of course a result set is a list. But I’m not talking about product types, complex or otherwise. I’m talking about sum types (also known as Algebraic Data Types).

> A row is an object and a row containing a reference to a row in another table is equivalent to an object containing another object if you're already thinking of it that way then it's a small jump to the result set as a set , that can participate in a union - and there's your sum type. add or project a type indicator and it's literally a tagged union.

That's not type safe.

Re: Old, Good Database Design

#153

Post by markus_zhang »

sixdimensional wrote: post #122
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 -…

Thanks! Yeah you are right, Vertica is good at OLAP and DWH. I'm just torn between wide table and dimensional data modelling and it's Vertica's problem. I'm probably attacking the wrong monster here.

I'm wondering if you have any source for OLAP data modelling under big data and columnar database?

Re: Old, Good Database Design

#154

Post by gfody »

throwaway894345 wrote: post #152
gfody wrote: post #149

Earlier quoted context omitted.

> A row is an object and a row containing a reference to a row in another table is equivalent to an object containing another object if you're already thinking of it that way then it's a small jump to the result set as a set , that can participate in a union - and there's your sum type. add or project a type indicator and it's literally a tagged union.

That's not type safe.

honestly I don't see how it's not - do you want to be able to union an int with a float or something? there's sql_variant or you can make your own type but that's pretty gross, violates 1NF, and is the opposite of type-safety IMO

Re: Old, Good Database Design

#155

Post by lmm »

jayd16 wrote: post #138
lmm wrote: post #131

Earlier quoted context omitted.

> 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? You can do that but it's a bodge. E.g. you won't be able to have the foreign key constraints you'd normally have on that login_id column. And good luck writing a query that actually does something differently for each…

You can have a table or view of all the ids across your implementations and FK into that. As for how you would model it in the application, in this case you can just normalize across all the possible columns and have the orm build out your mapped object.

> You can have a table or view of all the ids across your implementations and FK into that.

That's just moving the problem around. That table would have to have a bunch of nullable columns, and there's no way to express the constraint that either these columns are non-null or those columns are non-null, or these columns are non-null when this enum has this value.

I'm guessing you've never seriously used a language with first-class sum types. Yes, you can use hacks to represent sum-typed data in languages that don't have proper sum types, but it's always going to be a hack. It's like saying C has OO support because you can always construct virtual function tables by hand.

Re: Old, Good Database Design

#156

Post by chrisandchris »

rdiddly wrote: post #75
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.

Yes. And then you‘re app grows and now you have an undocumented NoSQL database that would better have been a RDMS...

There‘s nothing wrong with starting the proper way.

Re: Old, Good Database Design

#157

Post by jonathanzufi »

shoguning wrote: post #110
crgwbr wrote: post #57

Earlier quoted context omitted.

I believe OP is just referring to the ability to run adhoc queries. Typically nosql solutions are built to be performt for common access patterns, at the expense of being difficult or impossible to query in unplanned ways. SQL DBs are very good in that regard.

Thanks! And yes, I agree. The ability to write ad hoc SQL is a great skill.

Totally. I’ve always admired SQL wizards and their ability to (quickly) produce the most optimal queries given the relatively limited syntax (compared to what you can do with frameworks like, for example, LINQ today).

Re: Old, Good Database Design

#158

Post by commandlinefan »

burnstek wrote: post #144
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 imagining an industry where half of developers don't know how to design a relational data model. Scary if true.

I'd be surprised if it were only half.

Re: Old, Good Database Design

#159

Post by throwaway894345 »

gfody wrote: post #154
throwaway894345 wrote: post #152

Earlier quoted context omitted.

That's not type safe.

honestly I don't see how it's not - do you want to be able to union an int with a float or something? there's sql_variant or you can make your own type but that's pretty gross, violates 1NF, and is the opposite of type-safety IMO

Tagged unions as you describe are dynamic types. With a fair amount of effort, a SQL query can perhaps use the tag column to make sense of the other columns in the union at runtime, but the type system doesn't have access to that information. You can get part of the way there with constraints, but it's tedious and incomplete.

Re: Old, Good Database Design

#160

Post by throwaway894345 »

yummybrownies wrote: post #150
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…

Three techniques for sum types in SQL (absorption, separation, and partition): https://www.parsonsmatt.org/2019/03/19/sum_types_in_sql.html Also, here is a relational database system with native support for sum types (and also no "NULL" nonsense, which is also not part of pure relational algebra): https://github.com/agentm/project-m36

Oh, very cool. I'll check those out. Thanks for sharing!
Post reply on HN