141–150 of 167 posts
Earlier quoted context omitted.
Or, like myself, they chose NoSQL because it was much better integrated into their development and production cloud infrastructure than any alternatives.
Example or it didn't happen
Earlier quoted context omitted.
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…
But I’m not talking about product types, complex or otherwise. I’m talking about sum types (also known as Algebraic Data Types).
Earlier quoted context omitted.
How does the vehicle table ID column enforce referential integrity with the primary keys of the other columns? In general, there are a lot of issues that come up with respect to type safety and query semantics when you use these workarounds. They’re okay, but disappointing.
You can enforce that the column is non-null and has a valid value id in the relation table. You can also enforce that your motorcycle and auto tables are referenced in the relation table or make it a view of all the ids across the types (conceptually anyway, I'd have to look into the perf). It's a contrived example but what is the issue? What are the drawbacks? These are pretty easy constraints to add.
Honestly it’s been several years since I’ve done standard app development, so I’ve forgotten some of the specific problems that I would run into. I just remember that I tried all of the gimmicks to emulate sum types and they were always clumsy. Not a very satisfying rebuttal, I know.
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.
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?
Or, as I do: have a jsonb blob in one column, and a normal sql field in another, and do the sql joins with that.
So I have some data fields as sql columns, and some as jsonb properties.
Earlier quoted context omitted.
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
> A well-thought design can save us many hours of coding, testing, and troubleshooting. That is the very definition of a waterfall design model. I've turned into a fluid-design advocate over the years, where every design principle follows a next question - "okay, this is good but how would I change it?". So you start with a unique constraint and four months later, you find out that it is not actually unique (like "tw…
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.
What's wrong with TEXT type for postgres, mysql, etc? In Postgres you don't need to declare a length for varchar either.
There is no performance difference among these three types, apart from increased storage space when using the blank-padded type, and a few extra CPU cycles to check the length when storing into a length-constrained column.
https://www.postgresql.org/docs/current/datatype-character.h...
Earlier quoted context omitted.
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…
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).
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.
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…
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):