Is the diagram a joke? That's total ERD spaghetti.
Old, Good Database Design
61–70 of 167 posts
Re: Old, Good Database Design
#62Hi, my apologies if it's a bit off topic, but I wonder if you have any advise or can point me in some direction on the way to becoming a DBA, or something similar enough? I've been working as and old school sysadmin for the last 17 years and counting. Even if I can do nowadays devops stuff, I always liked the DBA work, based on my experience as sysadmin for DB servers (mysql, oracle, and friends...), and I'm considering moving my career in that direction. So is there any "DBA certification" for which I could take a course and all that? Thanks!!
Re: Old, Good Database Design
#63Hi, my apologies if it's a bit off topic, but I wonder if you have any advise or can point me in some direction on the way to becoming a DBA, or something similar enough? I've been working as and old school sysadmin for the last 17 years and counting. Even if I can do nowadays devops stuff, I always liked the DBA work, based on my experience as sysadmin for DB servers (mysql, oracle, and friends...), and I'm consider…
You've hit on the major problem with DBA work, which is that you are touching some of the most expensive prod stuff and even if you have "that cert" many places just want to see that somewhere else trusted you as a DBA for that type of product.
I managed to transition in a company in dire straits that had no other options, and then getting new DBA jobs was fairly easy.
Re: Old, Good Database Design
#64Reminds me of this great Derek Sivers post: https://sive.rs/pg If your design is good, you need less code.
>If your design is good,
"Draw the rest of the fucking owl"
Good design is hard. There are arguments to be made for both, but the problem with "Old, Good Database Design" is when the design changes it either devolves into
1. Downtime trying to move X billion rows
2. Some ad-hoc K/V store on top of your RDBMS
And most companies tend to opt for (2) rather than (1). It's no surprise that some systems just decide to choose (2) from the onset.
Re: Old, Good Database Design
#65Hi, my apologies if it's a bit off topic, but I wonder if you have any advise or can point me in some direction on the way to becoming a DBA, or something similar enough? I've been working as and old school sysadmin for the last 17 years and counting. Even if I can do nowadays devops stuff, I always liked the DBA work, based on my experience as sysadmin for DB servers (mysql, oracle, and friends...), and I'm consider…
You've hit on the major problem with DBA work, which is that you are touching some of the most expensive prod stuff and even if you have "that cert" many places just want to see that somewhere else trusted you as a DBA for that type of product. I managed to transition in a company in dire straits that had no other options, and then getting new DBA jobs was fairly easy.
Thanks for your feedback, and I pretty understand what you mean, I've worked with some DBA know as the "million dollars error guy". But I think that's a second step, and anyway we were maintaining the servers where those dbs were running... So we deserve some trust maybe?
Re: Old, Good Database Design
#66Some 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.
> have determined that an RDBMS will generate too much overhead
I think the read/write overhead is mostly a function of schema design, rather than an intrinsic property of an RDBMS. Denormalized schemas have similar performance profiles to document-oriented storage.
Moreover, mainstream SQL databases like Postgres are getting better and better at indexing jsonb fields, indexing time series data with BRIN, rudimentary full-text search, offering a one-size-fits-all storage system that may not be the best at everything, but will be good enough to support a growing business to its next funding round.
Re: Old, Good Database Design
#67If 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…
What's difficult about them? I typically use nullable columns and then a check constraint to specify a custom condition for nullability. Columns belonging to the same alternative in the sum type must be all null or all not null. And then there's check only one active alternative.
Re: Old, Good Database Design
#68Earlier quoted context omitted.
Could you suggest resources (books, articles, videos, moocs or others) to learn good database design. I am picking up skills about sql but want to better understand and learn about databases. As someone who doesnt have that background, a lot of the times I am just googling for stuff and just trying out bits and pieces.
I'd like to know this as well. I think you'll just have to build things (potentially horribly) and fail. I took three semesters of database (granted, baby database classes) and I still have no idea how you can do something pretty straightforward like creating a room reservation system. If there is a reservation beginning at 10:15 AM and ending at 12:30 PM and someone tries to book a reservation from 10:00 AM to 10:30…
Exclude constraint on a GiST index?
Re: Old, Good Database Design
#69Earlier quoted context omitted.
Could you suggest resources (books, articles, videos, moocs or others) to learn good database design. I am picking up skills about sql but want to better understand and learn about databases. As someone who doesnt have that background, a lot of the times I am just googling for stuff and just trying out bits and pieces.
I'd like to know this as well. I think you'll just have to build things (potentially horribly) and fail. I took three semesters of database (granted, baby database classes) and I still have no idea how you can do something pretty straightforward like creating a room reservation system. If there is a reservation beginning at 10:15 AM and ending at 12:30 PM and someone tries to book a reservation from 10:00 AM to 10:30…
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.
Re: Old, Good Database Design
#70If 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…
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?