Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Mistakes Beginners Make When Working with Databases

craigkerstiens.com

Post reply on HN
Print view View original

21–30 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#21

Post by nsxwolf »

epalmer wrote: post #11
knucklesandwich wrote: post #8

IMO normalizing up front is rarely ever a mistake. The primary advantage of normalization is it makes your schema amenable to changes in requirements. What if category ends up needing a description, and an id for a permalink-able page with links to each post in a category, etc.? Normalize up front and then denormalize where you need to when the requirements crystalize and your focus becomes responsiveness.

Only as a general rule: I have found that write once read mostly tables (history tables) are great candidates for denormalizing. State tables (read & write) usually benefit from 3rd form normalization.

Right, which I never understood until the first time I was tasked with building a data warehousing and reporting application. My naive 3NF schema approach led to joins that crippled report performance, and really messy schemes to deal with underlying changes in the application's schema over time.

Re: Mistakes Beginners Make When Working with Databases

#22

Post by scaryclam »

knucklesandwich wrote: post #8

IMO normalizing up front is rarely ever a mistake. The primary advantage of normalization is it makes your schema amenable to changes in requirements. What if category ends up needing a description, and an id for a permalink-able page with links to each post in a category, etc.? Normalize up front and then denormalize where you need to when the requirements crystalize and your focus becomes responsiveness.

I think his example was rather poor as I can think of at least 3 use cases off of the top of my head where having separate categories would be a good idea, and having varchar arrays would be a giant PITA.

That said I'm not sure he was trying to make the point that upfront normalisation is bad in itself, just that doing too much "what if maybe someday we might want to separate this" kind of normalisation is a waste of time.

Re: Mistakes Beginners Make When Working with Databases

#23

Post by zzzeek »

Please don't use UUID primary keys, at least by default. Not only will your PK indexes take up 4x the space, all of the foreign keys referring to it will also need indexes which also blow up in size, etc. I've seen very poor performance due to this issue. A surrogate PK is still a surrogate PK, so you're committing a relational algebra sin in any case (one which IMO is necessary for practicality concerns).

Re: Mistakes Beginners Make When Working with Databases

#24

Post by ashmud »

Illniyar wrote: post #6
mjw_byrne wrote: post #4

Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have. I think this isn't a question of ints being a…

With scale its not about key exhustation, its the ability to be able to create a key in multiple servers without having to consult a central location.

For processing data types without quality natural unique keys, being able to generate a unique key beforehand is very useful. I've written code both ways: inserting to get an auto-incremented integer, and generating a UUID, and the UUID approach was much easier to write and maintain.

Re: Mistakes Beginners Make When Working with Databases

#25

Post by [deleted] »

knucklesandwich wrote: post #8

IMO normalizing up front is rarely ever a mistake. The primary advantage of normalization is it makes your schema amenable to changes in requirements. What if category ends up needing a description, and an id for a permalink-able page with links to each post in a category, etc.? Normalize up front and then denormalize where you need to when the requirements crystalize and your focus becomes responsiveness.

[deleted]

Re: Mistakes Beginners Make When Working with Databases

#26

Post by nsxwolf »

perezdev wrote: post #13

Number 2 is kind of funny: >>The unfortunate part is: pagination is quite complex, and there isn’t a one-size-fits-all solution. Probably not. But the example he gave is a one size fits most. I've used it on a dozen different projects and have never had performance issue.

And I think when you do encounter a performance issue with LIMIT ... OFFSET, you have to ask yourself "Is a standard CRUD interface pattern really the best way to work with such a large data set?"

Re: Mistakes Beginners Make When Working with Databases

#27

Post by vesak »

endymi0n wrote: post #16

Pretty weak, partly terrible advice. - Storing images and blobs: granted, usually not a good idea - Limit/offset will take you a veeeery long way until you have to think about stuff like deep paging. And however you try to tackle that, if the stuff you paginate needs ordering, it's simply a hard problem and not a mistake. - UUID primary keys? Horrible advice that's only applicable at Google/Facebook scale (and even t…

>UUID primary keys?

Also somewhat useful to have when your organization decides that a multimaster cluster is a requirement.

Re: Mistakes Beginners Make When Working with Databases

#28

Post by hipjim »

Point 1 is silly - this depends greatly on what your application needs to do. If you need to store the image together with the metadata or other information related to the image that is transactional data - then storing the image in the database is the way to go. This also helps a lot with database backups, migrations etc..

Re: Mistakes Beginners Make When Working with Databases

#29

Post by falcolas »

mjw_byrne wrote: post #4

Not sure about point 3 ("use UUIDs instead of integer PKs"). Exhausting a 32-bit int takes a lot of usage; exhausting a 64-bit one is completely out of reach for almost everyone. 128-bit UUIDs will take more space to index than ints or bigints and are less human-readable. Depending on what UUID version you use you may or may not lose ordering, which can be a nice-to-have. I think this isn't a question of ints being a…

Personally, understanding how the engines usually work under the hood [0], I'd keep an integral PK, and have a `UNIQUE` external ID field.

[0] Having a string for a primary key will typically make there be a hidden integer primary key; better to have it explicit than implicit.

Re: Mistakes Beginners Make When Working with Databases

#30

Post by mslot »

knucklesandwich wrote: post #8

IMO normalizing up front is rarely ever a mistake. The primary advantage of normalization is it makes your schema amenable to changes in requirements. What if category ends up needing a description, and an id for a permalink-able page with links to each post in a category, etc.? Normalize up front and then denormalize where you need to when the requirements crystalize and your focus becomes responsiveness.

But what if you need to partition and possibly scale out part of your database? At that point a normalized data model becomes a huge road block. A balanced approach to normalization often allows you to more adaptive to requirements. If possible, it should be avoided for tables that can grow large.
Post reply on HN