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

61–70 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#61

Post by knucklesandwich »

scaryclam wrote: post #22
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…

Granted. In my experience wildly changing requirements are more common than unexpected need to scale though. "Design for the most likely needs" is my general and unspecific advice. YMMV.

Re: Mistakes Beginners Make When Working with Databases

#63

Post by danso »

This is much more common in spreadsheets, where free-wheeling data entry is encouraged, but a novice problem I see in real-world data structuring scenarios is breaking a column's enumeration...e.g. for a column that is supposed to be zip codes, doing something like "90210 and 90240", instead of creating a second column to handle edge case notes.

Related to that: inconsistent values for NULL/False, e.g. "No", "", "False", "false", etc...(or rather, not using a boolean type to enforce this).

And related to that: general unawareness of what NULL means. It's not the same as "" or "False" or 0, both in a technical sense and in a real-world sense.

Re: Mistakes Beginners Make When Working with Databases

#64

Post by Mister_Snuggles »

hipjim wrote: post #28

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..

Yup.

Not every PostgreSQL-backed application is a public-facing web app. A fat-client that connects directly to the PostgreSQL database to do "stuff" with transactional data will have a much easier time if any related images are stored alongside the transactional data.

Re: Mistakes Beginners Make When Working with Databases

#65

Post by knucklesandwich »

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.

Agreed, I should have made that clear. Anything log-like (events stores, history, "checkins", etc. is excepted from this). Analytics in particular is a case where large collections are the norm, even with low user counts. I contend that it's exceedingly rare for a beginner to be in the position of having these kinds of considerations though.

Re: Mistakes Beginners Make When Working with Databases

#66

Post by dragonwriter »

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? Horrible advice that's only applicable at Google/Facebook scale

UUID primary keys aren't needed for big keyspaces, they are needed when you need multiple processes (especially a flexible number of multiple processes) to generate unique IDs independently of each other and the central database, which will eventually get stored in the central database. This can be important at much smaller than Google/Facebook scale, depending on the use case.

Re: Mistakes Beginners Make When Working with Databases

#67

Post by mikeash »

Jweb_Guru wrote: post #35
JoeAltmaier wrote: post #31

Earlier quoted context omitted.

Its sad that UUIDs are not handled well yet, even in 2016. Its a final id solution that clearly avoids all id collision issues.

UUIDs don't avoid all id collision issues, and it's far from final. They'll prevent you from using BRIN indexes effectively, for example. Timestamp plus unique server id is an arguably much better approach; on top of everything else, timestamps are actually useful, and they fit in 8 bytes.

Version 1 UUIDs are server ID (MAC address) plus timestamp. Your overall proposal is already included in "UUIDs." They don't fit into 8 bytes, though, so if you want to squeeze them down you'd have to come up with your own scheme.

If you do use this scheme, be careful that your timestamp is sufficiently granular that it's not possible to generate two identical IDs in quick succession....

Re: Mistakes Beginners Make When Working with Databases

#68

Post by joslin01 »

andysinclair wrote: post #20

I'm suprised by this point: "the database tends to be an after-thought in application design". Maybe it's a generational thing (I'm 40+), but I tend to always start with thinking about the data structures and design in conjunction with the UI design. Getting a solid database design in place early in the development cycle is critical to building a solid, stable system- you can of course alter the database as you start…

I'm 30- and I always start with the data first as well. Linus said good programmers always think data first, and that resonated a lot with me when I first started coding. As I got further along, I realized how true it was that application logic often follows the data model.

Treating data as an afterthought is a great way to have to be re-defining your application logic every time you re-define your data because you forgot something.

Re: Mistakes Beginners Make When Working with Databases

#69

Post by edw519 »

Nice starter list. Here's more, off the top of my head:

  - failure to lock rows with pessimistic locking
  - failure to consider optimistic locking when viable
  - data sets within a table (undernormalization)
  - too much duplicate app code that should be stored procedure
  - overuse of stored procedures that should be in app code
  - appending with insert
  - appending to datasets with variable length
  - "smart" keys that can never be changed without rewriting
  - underuse of indexing to slow read performance
  - overuse of indexing to slow update performance
  - records too big for good hashing (undernormalization)
  - columns with different typed data (when DBMS allows)
  - columns with different logical data (app driven)
  - enhancing by inserting columns instead of appending
  - poor or missing audits
  - poor or missing security
  - poor or missing archiving

Re: Mistakes Beginners Make When Working with Databases

#70

Post by SonicSoul »

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…

I found the images advice unnecessarily black and white. Of course there are cases where storing images or documents is the right move. few reasons i can think of: 1. you can leverage the same auth scheme as the rest of your data 2. audit trail 3. transactional support for image CRUD operations 4. leveraging replication features already offered by your DB 5. blobs can be searchable if the database supports full text search, super useful to search document contents. Edit: shortened some points
Post reply on HN