Mistakes Beginners Make When Working with Databases • Hacker News Board

Re: Mistakes Beginners Make When Working with Databases

#131

Post by combatentropy » Wed, Jun 08, 2016, 5:34 PM UTC

davidw wrote: post #72

dragonwriter wrote: post #66

Earlier quoted context omitted.

> 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 Goog…

Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, wh…

Postgres's concurrency control (https://www.postgresql.org/docs/9.5/static/mvcc.html) does fine with many clients adding rows with numeric IDs derived from a sequence. In Postgres a sequence is a database object that always returns the number 1 more than the number it last gave someone (This is customizable too, https://www.postgresql.org/docs/9.5/static/sql-createsequenc...).

With 64-bit integers (in Postgres, "bigint") then you can have over 9 quintillion rows before you run out of numbers.

UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs. In many cases this is not a design requirement but a design after too little research.

Re: Mistakes Beginners Make When Working with Databases

#132

Post by ryanbrunner » Wed, Jun 08, 2016, 5:34 PM UTC

olavgg wrote: post #90

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…

Storing images / blobs in a database is fine! https://sqlite.org/intern-v-extern-blob.html I just recently built a e-commerce site that stores the images in the database. But be careful, some ORM's will automatically fetch that blob together with the rest of your object.

The thing is, for most cases relevant to web apps, you don't even actually want binary data. Your application isn't going to touch the file itself anyway - a client somewhere is going to be the one processing and downloading the file.

There's never a circumstance where dealing with hundreds of kilobytes or more of data is going to be faster than a 50-character file name, regardless of where it's stored.

Re: Mistakes Beginners Make When Working with Databases

#133

Post by tajen » Wed, Jun 08, 2016, 5:35 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.

I've made a product which uses Postgres' unique capabilities (It's the only one with transactions covering DDL). Side-effect: Not supporting all DBMS is a no-no when you need funding/convincing anyone. Hence the lowest-common-denominator race for most software. Mine is doing well, thank you ;)

Re: Mistakes Beginners Make When Working with Databases

#134

Post by mercer » Wed, Jun 08, 2016, 5:35 PM UTC

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 agree, and I prioritize data structures myself as well, but only because I've been up here in the hizzle for the past three or so years (in a development career of six years) and I kept being reminded of it via the linus quote, and the whole functional programming kazoo.

My experience is that lots of people, including people with many more years of development on me, don't take this approach though.

Re: Mistakes Beginners Make When Working with Databases

#135

Post by mh8h » Wed, Jun 08, 2016, 5:36 PM UTC

muxxa wrote: post #113

davidw wrote: post #72

Earlier quoted context omitted.

Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, wh…

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

The problem is usually with the assumptions about natural fields being unique, or not change over time. One day you realize they can actually change, or they are not unique. Changing the data model at that time to support a different primary key would be very difficult.

Re: Mistakes Beginners Make When Working with Databases

#136

Post by dep_b » Wed, Jun 08, 2016, 5:38 PM UTC

Oh god somebody gave the advice that you can normalize too much and some n00b will take it to heart.

Databases I've encountered that have been undernormalized: a gazillion Databases I've encountered that were too normalised: never

Until you are a Really Smart Dude(tte) working at a Really Important Company being paid accordingly you're probably not capable up front that your database does not need foreign keys, indexes and normalization and please just normalize like you've learned in databases 101, okay?

Re: Mistakes Beginners Make When Working with Databases

#137

Post by combatentropy » Wed, Jun 08, 2016, 5:40 PM UTC

muxxa wrote: post #113

davidw wrote: post #72

Earlier quoted context omitted.

Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, wh…

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

A natural primary key is best, yes, if you can find it.

Re: Mistakes Beginners Make When Working with Databases

#138

Post by farnulfo » Wed, Jun 08, 2016, 5:41 PM UTC

About default values on NULL columns, Oracle 11g and 12c have some interesting optimizations : http://www.oracle.com/technetwork/articles/database/ddl-opti...

Re: Mistakes Beginners Make When Working with Databases

#139

Post by brianwawok » Wed, Jun 08, 2016, 5:42 PM UTC

muxxa wrote: post #113

davidw wrote: post #72

Earlier quoted context omitted.

Oh yes! We ran into precisely this problem when we had a number of machines that mostly run independently, that needed to communicate with a central server. Integer ID's don't work well for that, and especially in light of the fact that Mysql reuses them in certain circumstances. I couldn't do anything about Mysql being on the machines, but used Postgres on the server, of course. The second generation of machines, wh…

Am I the only one who thinks that primary keys should be derived from the actual data? That way it's impossible for two processes to accidentally create the same conceptual piece of data (which is still possible with uuids). It also makes it much easier to recover from situations when you have to quickly promote a slave to a master role without first verifying that the slave is up to date. The main bonus though is th…

Okay so you run a school and want to make a students database. What do you use a a PK?

1) Social security #? Fails when you have international student.

2) Last Name, First Name, Middle Name? Fails when you have a repeat name.

3) Last Name, First Name, Middle Name, Home Town, Start Year? I guess this works for most of the time. Now you need to join to this table from the classes table. So now you need all 5 keys duplicated in the classes table to do the join.

Simple Integer primary keys "suck" in that you are adding bogus data to your database that has no value.. but man, they sure solve a LOT of problems. You need to think fairly hard before you get rid of them. In some cases you totally can. But making your default datamodel include an Integer PK solves a LOT of problems.

Re: Mistakes Beginners Make When Working with Databases

#140

Post by h43k3r » Wed, Jun 08, 2016, 5:44 PM UTC

falcolas wrote: post #29

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.

Can you point me to some docs that explain this ?