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

131–140 of 167 posts

Re: Old, Good Database Design

#131

Post by lmm »

jayd16 wrote: post #125
lmm wrote: post #102

Earlier quoted context omitted.

Imagine a system that allows third-party login (Facebook / AppleID / whatever) - then the account has either a username/password hash or an oauth token or some other kind of structured data. Delivery addresses for a system that supports both physical and digital products - you want a type-level distinction between physical and digital addresses, but an order might be being shipped to either. Subscription vs free tria…

>Imagine a system that allows third-party login (Facebook / AppleID / whatever) - then the account has either a username/password hash or an oauth token or some other kind of structured data. What I've seen most often is you have to deal with account merging but lets say you do really want either/or... Wouldn't you just have your third party tables (each with their own idiosyncrasies) and in your user table you'd hav…

> Wouldn't you just have your third party tables (each with their own idiosyncrasies) and in your user table you'd have login_type and login_id columns? You know which table to hit by type using the id?

You can do that but it's a bodge. E.g. you won't be able to have the foreign key constraints you'd normally have on that login_id column. And good luck writing a query that actually does something differently for each case - you'd have to do something like multiple left joins and there's no way to check you've handled all the cases and not done one of them twice.

Re: Old, Good Database Design

#132

Post by throwaway894345 »

jayd16 wrote: post #120
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…

Hmm, is it that hard? Vehicle table -- ID, TypeId, Make, etc. (123, 456, ...) TypeId table -- ID, Type (456, motorcycle) Motorcycle table -- ID, HandleBarStyle, etc. (456, Low Rider, ...) Automobile table -- ID, TrunkSpace, etc. (789, ...) You can pretty easily add extra information to any id as long as you know where to look, and that can be a simple enum column to define the concrete type (and thus what data to gra…

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.

Re: Old, Good Database Design

#133

Post by throwaway894345 »

ludston wrote: post #118
throwaway894345 wrote: post #48

Earlier quoted context omitted.

It seems that the relational model plainly enough wants to be the gate keeper for your data model—it gives extensive tools for modeling and enforcing data schema, but it just kind of throws its arms up at data that is “OR” shaped. Some people argue that it’s because there’s not an obvious way to lay out sum type data in memory or on disk or on the wire, but these problems are all solved by traditional programming lan…

I'm interested in understanding what you mean. What is "OR" shaped data? Are you thinking of data like, "The staff member must have either a salary or an hourly rate"? Typically I would see this modelled with two db columns, with a DB constraint indicating that only one of these can have a value.

That’s generally what I’m talking about, and it works well enough for simple cases, but these sorts of solutions tend not to scale well and you give up type safety. Not the end of the world, just disappointing.

Re: Old, Good Database Design

#134

Post by nesarkvechnep »

hazz99 wrote: post #93
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.

Example or it didn't happen

Re: Old, Good Database Design

#135

Post by klysm »

60secz wrote: post #24

Haven't you seen ghostbusters? "Don't cross the streams. It would be bad." If your data model has lines crossing over, it's the first smell of a bad design. Seriously tho, 5 minutes of untangling would make that data model diagram 100x better.

I wonder if there’s actually any significance to if your data model forms a planar graph

Re: Old, Good Database Design

#136

Post by outadoc »

gfody wrote: post #90
talaketu wrote: post #70

Earlier quoted context omitted.

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?

postal address is one of those cases where you probably do just want to shove it all in a string as most structural constraints eventually backfire - especially if you support international: http://www.columbia.edu/~fdc/postal/ the most common schema I've seen is usually something like line1, line2, line3, city, state, country, zip, etc. if it's a reporting database then city/state/country/zip is often mashed into so…

Watch out for `state` as well, please don't make it mandatory like so many websites. No, a region is not the equivalent of a state in France, you don't need it for my package to get there!

Re: Old, Good Database Design

#137

Post by jayd16 »

throwaway894345 wrote: post #132
jayd16 wrote: post #120

Earlier quoted context omitted.

Hmm, is it that hard? Vehicle table -- ID, TypeId, Make, etc. (123, 456, ...) TypeId table -- ID, Type (456, motorcycle) Motorcycle table -- ID, HandleBarStyle, etc. (456, Low Rider, ...) Automobile table -- ID, TrunkSpace, etc. (789, ...) You can pretty easily add extra information to any id as long as you know where to look, and that can be a simple enum column to define the concrete type (and thus what data to gra…

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.

Re: Old, Good Database Design

#138

Post by jayd16 »

lmm wrote: post #131
jayd16 wrote: post #125

Earlier quoted context omitted.

>Imagine a system that allows third-party login (Facebook / AppleID / whatever) - then the account has either a username/password hash or an oauth token or some other kind of structured data. What I've seen most often is you have to deal with account merging but lets say you do really want either/or... Wouldn't you just have your third party tables (each with their own idiosyncrasies) and in your user table you'd hav…

> Wouldn't you just have your third party tables (each with their own idiosyncrasies) and in your user table you'd have login_type and login_id columns? You know which table to hit by type using the id? You can do that but it's a bodge. E.g. you won't be able to have the foreign key constraints you'd normally have on that login_id column. And good luck writing a query that actually does something differently for each…

You can have a table or view of all the ids across your implementations and FK into that.

As for how you would model it in the application, in this case you can just normalize across all the possible columns and have the orm build out your mapped object.

Re: Old, Good Database Design

#139

Post by woolcap »

throw345hn wrote: post #45
SamuelAdams wrote: post #40

Earlier quoted context omitted.

I am forever grateful that I took a full semester of database design in my undergrad. This single skill has stood with me for my entire career so far and has enabled me to figure out the root cause of many production issues. Plus people really like it when you can answer ad-hoc questions like "what inspections are still open and when were they first opened". If y'all can understand Angular / React / Vue there's no re…

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.

Just about anything written by C. J. Date would be worth your while in this regard. It will give you a solid grounding in the relational model, which you can then apply to any RDBMS. Here's a good list to start with:

- Database in Depth, O'Reily (2005)

- Relational Theory for Computer Professionals, O'Reilly (2013)

- SQL and Relational Theory, 3rd Ed, O'Reily (2015)

- Database Design and Relational Theory, 2nd Ed, Apress (2019)

Re: Old, Good Database Design

#140

Post by petepete »

slifin wrote: post #56

Most relational databases aren't relational enough For example in Drupal you can have a node table which is a foreign key relationship to many other custom tables In SQL I can't say get me all the nodes that have a start date without explicitly left joining to a potentially dynamic number of other tables using a higher level language to modify the query In Datomic or Datascript or Datahike or Datalevin or Crux this q…

You're judging all relational databases having looked at Drupal's approach? Ok...
Post reply on HN