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

21–30 of 167 posts

Re: Old, Good Database Design

#21

Post by alextheparrot »

brobinson wrote: post #14
simonw wrote: post #8

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.

Postgres also has a TEXT type like this. Edit: I see mpolun left a similar comment here, but it looks like he has been mostly shadowbanned for about 8 years.

Just for general interest, how can you see their comment or assert they have been shadowbanned?

Re: Old, Good Database Design

#22

Post by pizza234 »

simonw wrote: post #8

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.

I suppose that in every RDBMs that makes the distinction, it's an optimization matter - VARCHAR being stored on-page, while TEXT off-page (although there can be optimizations for short TEXT values); the latter will cause an extra page seek on access.

Some database [versions] may also be unable to apply certain optimizations in certain cases, when TEXT is used (eg. temp tables on MySQL That doesn't prevent one from always using TEXT, and possibly, for most of the use cases (surely, if one uses SQLite, that's the case), the performance impact is not meaningful.

Re: Old, Good Database Design

#23

Post by codetrotter »

brobinson wrote: post #14
simonw wrote: post #8

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.

Postgres also has a TEXT type like this. Edit: I see mpolun left a similar comment here, but it looks like he has been mostly shadowbanned for about 8 years.

> Edit: I see mpolun left a similar comment here, but it looks like he has been mostly shadowbanned for about 8 years.

Yeah same. I don't have enough karma to vouch for their comment but I see no big reason that said comment should be dead. Their account is from 2012 and the vast majority of their few comments seem to be contributing to the conversation. Their first dead comment is also from 2012 but at a glance said comment is the only one that stands out as not contributing much to the conversation. And that's being harsh even – I've probably made less substantial comments in the past myself. Yet like 30% of their 3 pages of comments are dead. And looking at their submissions they have ever only submitted 3 stories, 2 of which appear to be from a domain that they themselves control. Hardly enough to be subjected to having so many of their comments killed I think. Though of course there might be other factors at play, but from what I see on their profile page I see nothing bad enough to warrant this.

Re: Old, Good Database Design

#24

Post by 60secz »

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.

Re: Old, Good Database Design

#25

Post by roywiggins »

> we should keep numeric data in “integer”, “numeric” fields

I end up keeping numeric data as text when I'm ingesting an external data source that I don't trust not to change ID format on me. They're all numeric now, but the format could change, and the actual numeric value of the ID is not important at all.

Re: Old, Good Database Design

#26

Post by codetrotter »

alextheparrot wrote: post #21
brobinson wrote: post #14

Earlier quoted context omitted.

Postgres also has a TEXT type like this. Edit: I see mpolun left a similar comment here, but it looks like he has been mostly shadowbanned for about 8 years.

Just for general interest, how can you see their comment or assert they have been shadowbanned?

> how can you see their comment

Go to your profile and ensure you have showdead set to yes.

> assert they have been shadowbanned

See https://news.ycombinator.com/threads?id=mpolun and check out how many of their comments are showing as dead in combination with the contents of said comments. (The step above about setting showdead to yes might be required before you follow said link in order to actually see the dead comments). Almost every single one of the dead comments is contributing to the conversation. This is indicative of a shadow ban. HN users would not be downvoting the vast majority of these comments I think.

Re: Old, Good Database Design

#27

Post by commandlinefan »

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.

Re: Old, Good Database Design

#28

Post by bcrosby95 »

roywiggins wrote: post #25

> we should keep numeric data in “integer”, “numeric” fields I end up keeping numeric data as text when I'm ingesting an external data source that I don't trust not to change ID format on me. They're all numeric now , but the format could change, and the actual numeric value of the ID is not important at all.

Yes, be careful conflating numeric data with data that looks numerical. IDs fall into the latter for sure. Unless you have complete control over it, then it might always be numeric.

E.g. back when I thought I was super smart, on one project I made the credit card cvv a number. Except they can start with 0. Whoops.

Re: Old, Good Database Design

#29

Post by brobinson »

codetrotter wrote: post #23
brobinson wrote: post #14

Earlier quoted context omitted.

Postgres also has a TEXT type like this. Edit: I see mpolun left a similar comment here, but it looks like he has been mostly shadowbanned for about 8 years.

> Edit: I see mpolun left a similar comment here, but it looks like he has been mostly shadowbanned for about 8 years. Yeah same. I don't have enough karma to vouch for their comment but I see no big reason that said comment should be dead. Their account is from 2012 and the vast majority of their few comments seem to be contributing to the conversation. Their first dead comment is also from 2012 but at a glance said…

I have 1500 karma, but I didn't see an option to vouch for it... I have seen that option on other comments, though. Maybe dang can take a look at their account.

Re: Old, Good Database Design

#30

Post by geophile »

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…

> If one of the purposes of relational databases is data modeling

Huh?

Creating a data model before creating a database, is like writing an outline before writing an essay. It organizes your thoughts and gives structure to what you are about to do. Once you have a data model, you can then implement it using whatever database technology you choose.

If you don't start with a data model, you literally don't know what you are doing.

Post reply on HN