Mistakes Beginners Make When Working with Databases • Hacker News Board

Re: Mistakes Beginners Make When Working with Databases

#51

Post by _RPM » Wed, Jun 08, 2016, 4:23 PM UTC

hinkley wrote: post #47

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…

If you can avoid exposing those ID's then 64 bits should do fine with no problems. But it turns out JavaScript can only handle ~54 bit integers, so if you try to randomize your IDs to prevent people scanning your data, you'll be in for a rough patch making sure you always treat IDs as strings. At least with UUID you pretty much have to treat it as a string. You also avoid being the next developer in a long line who t…

The common pattern I've seen is to send the 64 bit integer back as a string. So it would be a "numeric string"

Re: Mistakes Beginners Make When Working with Databases

#52

Post by Someone1234 » Wed, Jun 08, 2016, 4:24 PM UTC

KurtMueller wrote: post #43

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 think the advice on shying away from db normalization is a terrible idea for beginners. If anything, they should try to stick to creating db tables in 3rd normal form until they learn when and where to bend those rules.

They're literally suggesting an "array" in a VARCHAR field (by which I assume they mean comma separated or similar). That's a dangerous habit to get into for a beginner, and how you wind up with so many over stuffed columns.

In the article's specific scenario I'd recommend they just create the darn tables rather than doing his hacky solution.

Re: Mistakes Beginners Make When Working with Databases

#53

Post by craigkerstiens » Wed, Jun 08, 2016, 4:26 PM UTC

Someone1234 wrote: post #52

KurtMueller wrote: post #43

Earlier quoted context omitted.

I think the advice on shying away from db normalization is a terrible idea for beginners. If anything, they should try to stick to creating db tables in 3rd normal form until they learn when and where to bend those rules.

They're literally suggesting an "array" in a VARCHAR field (by which I assume they mean comma separated or similar). That's a dangerous habit to get into for a beginner, and how you wind up with so many over stuffed columns. In the article's specific scenario I'd recommend they just create the darn tables rather than doing his hacky solution.

Original author here, what I'm proposing is an ARRAY[VARCHAR]. Postgres has a native array type, different from manually mangling an array.

Re: Mistakes Beginners Make When Working with Databases

#54

Post by Practicality » Wed, Jun 08, 2016, 4:26 PM UTC

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…

Agreed. And the reason here for using UUIDs is not accurate. UUIDs are for having multiple systems (or components) create IDs and having a reasonable expectation for uniqueness, hence the Universal Unique part.

It grinds my gears when people sneer at integers when they perform optimally or nearly optimally on the majority of use cases.

Re: Mistakes Beginners Make When Working with Databases

#55

Post by zer00eyz » Wed, Jun 08, 2016, 4:27 PM UTC

acveilleux wrote: post #2

If only developers learned more about the DB engines they use and their capabilities. I've seen so many bone headed decisions (EAV "schemas" being a recurring one) because it was easier to program around the DB than learn how to use it proper. It's not because a few classes insulate you from the bone headed SQL that it's a good idea.

EAV isn't all bad, there are use-cases for it. If you have a highly dynamic data (user configurable) then EAV might be a good way to STORE that data.

Is it ugly, it sure is. Moving read out to a cache layer, and search out a purpose built system, the DB becomes a persistence engine. You have solved the other issue you don't mention with EAV, and thats performance.

Edit (my ability to post should be forbidden till I have had coffee, cleaned up for clarity)

Re: Mistakes Beginners Make When Working with Databases

#56

Post by knucklesandwich » Wed, Jun 08, 2016, 4:27 PM UTC

mslot wrote: post #30

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.

If you're in this position, you have scale informing product requirements already and can denormalize up front. But this is uncommon for read/write transactional workloads and an especially uncommon scenario for a beginner to find themself in.

Re: Mistakes Beginners Make When Working with Databases

#57

Post by Someone1234 » Wed, Jun 08, 2016, 4:28 PM UTC

craigkerstiens wrote: post #53

Someone1234 wrote: post #52

Earlier quoted context omitted.

They're literally suggesting an "array" in a VARCHAR field (by which I assume they mean comma separated or similar). That's a dangerous habit to get into for a beginner, and how you wind up with so many over stuffed columns. In the article's specific scenario I'd recommend they just create the darn tables rather than doing his hacky solution.

Original author here, what I'm proposing is an ARRAY[VARCHAR]. Postgres has a native array type, different from manually mangling an array.

Fair enough. Still an ugly hack, and you're still over stuffing a column.

It does avoid some issues with string passing which is nice, but that's just about all it solves.

Re: Mistakes Beginners Make When Working with Databases

#58

Post by Mister_Snuggles » Wed, Jun 08, 2016, 4:29 PM UTC

ReedRothchild wrote: post #33

Not sure about point 1 as a blanket statement... many times external services are not allowed and therefore aren't accessible. How is S3 storing them? Probably in a DB. In some DB and application frameworks, IO issues with image data can and have been streamlined. If not, better model design might help. Putting them in S3 limits what you can do with the images. For basic application images, yeah that doesn't belong i…

This advice really only applies to public web sites.

PostgreSQL could be used as the backend for, as an example, an internal inventory tracking application. Something like this could allow users to upload a generic photo of the item in inventory. Putting these in S3 or a CDN makes no sense for an internal application.

And, of course, "internal application" doesn't necessarily mean a web application. It could be a fat-client (WinForms, Qt, Cocoa, etc) that connects directly to the database. It could connect to an application server (which then connects to the database) and speak some custom protocol. It could be a text-based terminal app. It could be that there are a variety of these applications that all connect to the same database.

Better advice would be:

Store images outside of the database. If you are building a public-facing web site or web application, something like S3 or a CDN may be a good fit. If you are building an internal web application, or are otherwise unable to use S3 or a CDN, storing images on a web server and storing the URL (or something that allows you to determine the URL) is a better option. In some cases, such as fat-clients that connect directly to the PostgreSQL database, you may find that storing images in the database is truly the best option. Keep in mind that this may impact performance in the following ways: (insert list of potential issues).

Re: Mistakes Beginners Make When Working with Databases

#59

Post by hinkley » Wed, Jun 08, 2016, 4:29 PM UTC

vesak wrote: post #27

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.

Or your customers start merging and acquiring each other.

Or you end up hosting your app for customers and want to go multitennant.

Re: Mistakes Beginners Make When Working with Databases

#60

Post by foxylion » Wed, Jun 08, 2016, 4:30 PM UTC

jayzalowitz wrote: post #5

point one may make a huge difference in page load time.... s3 is fast but it isn't no connection instant and cached like b64

I think he is talking about a web application. So providing a image tag with the URL, as a src attribute, to the image hosted on s3 seems like a better solution than embedding the image in the HTML response (and blocking the page rendering for a longer time).