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

151–160 of 209 posts

Re: Mistakes Beginners Make When Working with Databases

#151

Post by naasking »

horshod wrote: post #85
drumdance wrote: post #74

Earlier quoted context omitted.

Integer IDs are definitely easier, but I've seen it cause so many security issues -- "hmm, I wonder what happens if I manually type in /user/239?" IME It's easier to teach junior devs not to use integers than it is to get them to think holistically about security. This relates to the "sometimes security by obscurity is okay" post from yesterday.

I have faced this issue in my web application. My solution is to use a UUID wherever the ID will be exposed to the user (only one place in my application) and use an integer ID everywhere else. Although this does mean 2 IDs need to be generated and stored. The other solution is to never allow access to users without a log in.

You can avoid the need for two IDs if you HMAC the URL. There's a single private key on the server used to generate and verify the MAC on subsequent requests.

Re: Mistakes Beginners Make When Working with Databases

#152

Post by Clubber »

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 with database first. It's the essence of your application, meaning hypothetically, if you were to strip away all your UI and business logic, and all your users were experts at SQL and never made a mistake, the database is all you would have left.

Furthermore, a database mistake, once built on, will stay with your organization for life, only removable by a lot of pain. A coding mistake is fairly trivial to recover from.

I find it troubling that many self taught programmers don't take the time to learn databases. They aren't even that difficult to master. 3NF, Constraints, Indexes. That covers 90% of it. Everyone thinks they're making Google.

Re: Mistakes Beginners Make When Working with Databases

#153

Post by naasking »

ryanbrunner wrote: post #132
olavgg wrote: post #90

Earlier quoted context omitted.

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.

But if you replicate your DB, now you also have to replicate your filesystem. Easier to just segregate BLOBs in their own tables and let DB replication do all the work.

Re: Mistakes Beginners Make When Working with Databases

#154

Post by dragonwriter »

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…

> Am I the only one who thinks that primary keys should be derived from the actual data?

Where a valid natural key exists, it absolutely should be used. Surrogate keys should only be used where there isn't a natural attribute (or composite of such attributes) that corresponds to the unique identity of a tracked entity.

However, that's very common in the real world.

Re: Mistakes Beginners Make When Working with Databases

#155

Post by olavgg »

ryanbrunner wrote: post #132
olavgg wrote: post #90

Earlier quoted context omitted.

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.

Actually in some cases you want your application to touch that blob, especially if want to automatically generate thumbnails and have aggressive/best effort compression on that image. It is the best way to do something about users that uploads a 10MB image that is uncompressed :-)

Re: Mistakes Beginners Make When Working with Databases

#156

Post by davidw »

combatentropy wrote: post #131
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…

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, "b…

These were not 'remote clients' accessing PG directly, but independent computers that were not even on or connected to the network some of the time.

> UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs.

Exactly.

Re: Mistakes Beginners Make When Working with Databases

#157

Post by lloyd-christmas »

developer2 wrote: post #126
lloyd-christmas wrote: post #42

Earlier quoted context omitted.

> I find that many front end developers start with the User Design first, ignoring how the data should be stored or accessed This sentence seems contradictory. If you start with user design, you then know how the data will be accessed which allows you to store it with purpose. Obviously this shouldn't be at the expense of proper database design, but how your app functions defines how you should store your data. While…

>> how your app functions defines how you should store your data I think this is the fallacy the parent you are replying to is attacking. Your data should stand apart from your application. Applications change over time. Many databases also serve multiple applications (web, mobile, api, stats, etc.). Your data store should make sense on its own without being tied to an application's design. If you build your database…

> Many databases also serve multiple applications... Your data store should make sense on its own without being tied to an application's design.

While your point is valid, I think it's theory vs. practice. I think most databases don't get used for wildly different applications. I'd rather design my database for something that I know is performance-sensitive than design it for an imaginary application that might exist in the future. I've never experienced a case where our application changes dramatically enough that manipulating your database is anything serious enough to write home about. If you previously only had a shipping address and now you need a billing address as well, it's a pretty straightforward migration. Incremental change isn't hard.

Companies that become Medco are few and far between. Their database should default modeling one-to-one relationships as one-to-many just so someone might be able to use it more generically in the future. But that's because it's a realistic use-case. My point was that there is a balance to be had between stand-alone and real-life usage, and you shouldn't default to full generic just to make it stand-alone.

Re: Mistakes Beginners Make When Working with Databases

#158

Post by VLM »

ryanbrunner wrote: post #127
muxxa wrote: post #113

Earlier quoted context omitted.

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 there's very few examples of real-world data that actually matches a primary key - i.e. is guaranteed to be unique and never changes. I've been burned by this so many times. In reality, everything needs to at least have some capability to change.

Unique and never changes can be two things concatenated, not one thing.

If that concatenation is too long you can ram anything thru a hash to get a constant length smoothly distributed key. Its also fun to use "weak" hashes for this because it trolls wanna be security types who don't understand the application.

Given the above, some important things concatenated and hashed works. Can always add an application ID or importer ID or process ID or a timestamp of high enough resolution.

Obviously needs are different if you're trying to create a bank user database vs deduplicating sampled engineering data.

So something that's unique is NOW() (assuming low enough sample rate LOL) and something that never changes is a laser serial number, concatenate those and ram thru a hash to make it small and fit.

Re: Mistakes Beginners Make When Working with Databases

#159

Post by fotcorn »

davidw wrote: post #81
sshykes wrote: post #78

Earlier quoted context omitted.

Vehemently disagree. What you are proposing is everything wrong with security-by-obscurity. In this case the security hole of /user/123 just needs to be properly locked down. That is all.

I mostly use integers (but see my comment elsewhere). There is most definitely a german tank problem involved in them, though: https://en.wikipedia.org/wiki/German_tank_problem For most things I do, it's not a concern, but it is something to keep in mind.

You can use hashids for this problem [0] This just "encrypts" the primary key with a given salt.

[0] http://hashids.org/

Re: Mistakes Beginners Make When Working with Databases

#160

Post by KurtMueller »

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.

I'm sorry, but I still must disagree with this advice as it pertains to beginners. I would rather a beginner "overnormalize" their database tables rather than have them default to an array column type. It's much easier to transition from "overnormalized" db tables to one that uses array columns when appropriate than it is to go the opposite direction.
Post reply on HN