3 ms·
You can already do that by having circle and rectangle relations. Union types dos not give you any additional power compared to relations. Your proposal might
by goto11 5y ago
You can already do that by having circle and rectangle relations. Union types dos not give you any additional power compared to relations.
Your proposal might be more convenient though than creating multiple relations, so we should look into making it just as convenient to create the necessary relations to express this. So lets say your syntax proposal creates multiple relations under the hood - then I'm all aboard!
The "everything is a relation" is a really powerful concept in the relational model. Adding composite column types like arrays, unions etc. are still less powerful than just using relations. But people like it because the syntax is more convenient to use.
- NavinF 5y agoAgreed, that would solve my aesthetic concerns and kinda solve OP's XY problem with JSON. But the performance of auto-created tables for every union member would kinda suck if you never take advantage of the relation and still pay the cost. You'd also have to auto-create indexes so that queries that read full objects is as fast (IOPS) as storing BLOBs. I was imagining a future database that takes this a step further and lets you define named parameterized queries next to your tables and chooses the best implementation of unions based on your queries. That's what I meant by "without thinking about it". We already do this for algorithms (query optimizer), and I'd love to see it work with data structures
- goto11 5y agoThe basic premise of the relational model is that the physical storage structure and optimizations (including indexes) are independent of the logical model. A table could be stored row-by-row or column-by-column or any other clever way without it affecting the schema and queries - although it will affect the performance of the queries.
- laszlokorte 5y agoBut if I have a circle relation and a triangle relation, how do I create a foreign key for the favourite_shape column in my user relation?
- laszlokorte 5y agoThinking a bit more about it I think this would be a very elegant solution for Tagged-Unions in SQL: - simply allow tagged unions for foreign key constraints: CONSTRAINT my_fk FOREIGN KEY (own_column) REFERENCES EITHER table_a (a_id) OR table_b (b_id) OR table_c (c_id) - add join syntax to join via defined foreign key: FROM own_table JOIN VIA my_fk The union FK would store an additional flag determining the target table and the JOIN VIA would switch on that flag and it would be allowed to use any columns from all target tables in the query but only the matching ones would be not null.
- goto11 5y agoIf you put the foreign keys on the table_a/b/c tables, then you can express the same without having to introduce tagged unions.
- laszlokorte 5y agoAs I said in response to a sibling: But can you can not (in a simple way) prevent that both a circle and a triangle point to the same user and you can not enforce a NOT NULL for a user to must have a favourite shape.
- goto11 5y agoYou would have the foreign key in the circle/triangle relations pointing to favourite_shape rather than the other way around.
- laszlokorte 5y agoBut can you can not (in a simple way) prevent that both a circle and a triangle point to the same user and you can not enforce a NOT NULL for a user to must have a favourite shape.
- goto11 5y agoYou can do that with a check constraint, although I don't know if you consider it simple. It would be cool with better support for common constraint patterns.