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

91–100 of 167 posts

Re: Old, Good Database Design

#91

Post by throwaway894345 »

PhineasRex wrote: post #78
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

That matches my intuition. The more surprising thing is that this doesn't seem to be talked about very much. I've literally never heard anyone else advocate for a relational database with support for sum types (I'm sure they exist) which has caused a lot of introspection about whether or not there's something about RDBMSs that I just don't understand.

Re: Old, Good Database Design

#92

Post by sk5t »

gavinray wrote: post #82
sk5t wrote: post #69

Earlier quoted context omitted.

The documentation for Postgresql range types describes how to do exactly this. https://www.postgresql.org/docs/11/rangetypes.html#RANGETYPE... Edit: and if you didn't want to use postgres, you could have "starttime" and "endtime" columns and reject any bad bookings with a before insert / before update trigger.

This approach is the best and works really well if you don't need to do a join on a related table to look up information. If you need to use data outside of the current table for exclusions/check constraints, you have to write a trigger function (as far as I know). I had to solve this recently, where the actual start/end times were stored on a related table. I'm no SQL wizard, but I'd love to share my solution in cas…

TBH it's a bit more verbose and less performant than need be, but hey, if it works, rock on! But don't use 'select *' in production code.

Consider next time something like

    if exists (select from sometable t1
      join sometable t2 on 
        t1.resource_id = t2.resource_id 
        and t1.res_id  t2.res_id
        and tstzrange(t1.start, t1.end) && tstzrange(t2.start, t2.end)
      where t1.res_id = new.res_id )
then ... raise exception

Re: Old, Good Database Design

#93

Post by hazz99 »

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.

Or, like myself, they chose NoSQL because it was much better integrated into their development and production cloud infrastructure than any alternatives.

Re: Old, Good Database Design

#94

Post by ragnese »

simonw wrote: post #8

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.

Don't the other ones have TEXT, too?

Re: Old, Good Database Design

#95

Post by teej »

markus_zhang wrote: post #89
teej wrote: post #85

Earlier quoted context omitted.

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

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

No amount of technology can fix a dysfunctional org.

A large amount of the work of BI is getting data into the right shape in order to ask questions about it.

- What’s level on average do new players drop off?

- How are sales of the new skins we introduced performing vs the last set?

- How often are players engaging with feature A vs B?

You might be able to answer all of these questions with just event data in a wide table, but you’ll write very different queries to answer them. dbt is a way to write queries on your source data that leads to systematic, repeatable, and reusable new tables for answering analytics questions.

Re: Old, Good Database Design

#96

Post by gfody »

throwaway894345 wrote: post #91
PhineasRex wrote: post #78

Earlier quoted context omitted.

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

That matches my intuition. The more surprising thing is that this doesn't seem to be talked about very much. I've literally never heard anyone else advocate for a relational database with support for sum types (I'm sure they exist) which has caused a lot of introspection about whether or not there's something about RDBMSs that I just don't understand.

it came and went, postgres and sql server were (and still are) referred to as "object-relational" because of their support for rich user-defined complex types. it's just not that practical real-world because we tend to start with first normal form which complex types violate naturally.

> whether or not there's something about RDBMSs that I just don't understand

perhaps if you consider the entire result of some query to imply the type of the thing being modeled rather than any given table or cell, then it's more obvious that the basic types supported aren't much of a limitation? afterall you can always include a "type" indicator (or project one from a more sophisticated relational model).

Re: Old, Good Database Design

#97

Post by bleepblorp »

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?

Don't even try to do validation on postal addresses. The postal system has so many corner cases that you'll never be able to correctly handle all of them. Every mishandled corner case will cost you, or your counter-party, time and money.

Just dump addresses into a unicode string and let the postal system figure it out.

Re: Old, Good Database Design

#98

Post by unnouinceput »

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.

Re: Old, Good Database Design

#99

Post by PaulStatezny »

Supermancho wrote: post #51
geebee wrote: post #31

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…

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

Re: Old, Good Database Design

#100

Post by namelosw »

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…

I tend to agree. Algebraic data types (product and sum types) is essential for modeling. Missing sum type would be incomplete in terms of modeling.

A lot of obscure OO code I have seen is related to modeling sum types of products (or parameterized enumerated in some language), or the lack of it in mainstream languages.

The problem applies to SQL as well. If I make everything into the same table and leave many columns empty I’m not modeling at all. But if I split them into tables it defeat most basic SQL functionalities like SELECT * FROM my_sum_type.

Post reply on HN