I don't know—this could be seen as more consistent. It's almost like a type system on identifiers. This way you avoid accidentally joining users.id to user_preferences.id because you'll always join with the user_id "type" from users.user_id to user_preferences.user_id. SQL even has a syntax for that: JOIN user_preferences USING (user_id).
The reason we use aliases is it almost only in toy-examples that most tables are called things like Order. I won't fill my SELECT with row after row with CustomerOrderRowHistory...
And your second example kind of prove my point. When it it written out like that it become obvious that you are trying to compare diffent quantities. There is clearly something wrong when you are trying to join on order_created_at = customer_created_at which isn't at all as clear with your way of writing.
From someone who's done a lot of data modeling, imo this is not good advice. I recommend naming your IDs "{table}_id". Then it's extremely clear what it refers to when it's used as a foreign key, and you can join naturally using SQL's 'using' statement as it's intended to be used.
In general, when building a data model for end users you're going to want to materialize as much as possible. This often means denormalizing as much as possible so that, instead of having a star schema where joins are performed on the fly, you have a few really wide tables (many many columns) with all of the relevant information for a given object available.
Where "materialization" means whether a relation is created as a view (rather than a table).
I'm not sure I agree with this. Personally, I think it's better to primarily normalize and write all queries as close to functional programming as possible. Which means opting for computation on the fly during development and relying on the underlying database infrastructure to optimize internally. Then denormalize or dupe data via views and caching after bottlenecks have been identified. Otherwise you're fighting premature optimization during development.
That said, there could be a place for materialization in log-structured storage (LSS):
I'm thinking that the future of all of this could be LSS on distributed consensus algorithms like RAFT, then devoting some amount of cache to building out materialized relationships that can always be derived from the log. Then we could have our cake and eat it too with fast durable writes and low-overhead reads without joins.
If anyone has an example of this, I'd love to see it.
10 comments
[ 3.5 ms ] story [ 36.5 ms ] threadWrong. Be consistent. All id's are named ID or id. Consistency is key.
Most of the article is filler and style choices.
And your second example kind of prove my point. When it it written out like that it become obvious that you are trying to compare diffent quantities. There is clearly something wrong when you are trying to join on order_created_at = customer_created_at which isn't at all as clear with your way of writing.
From someone who's done a lot of data modeling, imo this is not good advice. I recommend naming your IDs "{table}_id". Then it's extremely clear what it refers to when it's used as a foreign key, and you can join naturally using SQL's 'using' statement as it's intended to be used.
In general, when building a data model for end users you're going to want to materialize as much as possible. This often means denormalizing as much as possible so that, instead of having a star schema where joins are performed on the fly, you have a few really wide tables (many many columns) with all of the relevant information for a given object available.
Where "materialization" means whether a relation is created as a view (rather than a table).
I'm not sure I agree with this. Personally, I think it's better to primarily normalize and write all queries as close to functional programming as possible. Which means opting for computation on the fly during development and relying on the underlying database infrastructure to optimize internally. Then denormalize or dupe data via views and caching after bottlenecks have been identified. Otherwise you're fighting premature optimization during development.
That said, there could be a place for materialization in log-structured storage (LSS):
https://jvns.ca/blog/2017/06/11/log-structured-storage/
I'm thinking that the future of all of this could be LSS on distributed consensus algorithms like RAFT, then devoting some amount of cache to building out materialized relationships that can always be derived from the log. Then we could have our cake and eat it too with fast durable writes and low-overhead reads without joins.
If anyone has an example of this, I'd love to see it.