Mistakes Beginners Make When Working with Databases
craigkerstiens.com
craigkerstiens.com
- 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 they often use 64bit integer keys for a lot of entities, see Graph API or Adwords). Will wreak havoc on insert and join performance and index size. 64 bit is more than you'll ever need even for the most serious application.
- Default values on NULL columns: Okay advice, but a pretty random issue
- Going away from normalization is optimization that's mostly premature and regarding the drawbacks should only be done with utmost care and to resolve specific performance problems, not as a general approach.
Overall, pretty mediocre advice.
Also somewhat useful to have when your organization decides that a multimaster cluster is a requirement.
Or you end up hosting your app for customers and want to go multitennant.
(Edit: I realize I could go look this up myself, but 1) I like explaining things so perhaps you do too, and 2) I'm hoping perhaps at the very least you can point me to a good resource to properly understand this topic)
I'm on a train now, but I think there's also a timestamp element to it somewhere, making it less likely that a restart of the random number generator will cause you to have a collision.
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....
In the article's specific scenario I'd recommend they just create the darn tables rather than doing his hacky solution.
It does avoid some issues with string passing which is nice, but that's just about all it solves.
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 thinks they know what 'unique' 'random' mean but can't actually be trusted with that much responsibility (it's okay, most of us can't. I once stopped someone in the 11th hour from shipping a mutually authenticated SSL application that could only generate 256 unique AES session keys, due to a seeding bug. That was scary)
But it's usually set wrong by default, and if your IDs are monotonically increasing you'll probably never notice.
Or you could initialize all your tables with the first primary key being 2^32 and only lose one quarter billionth of your legal keyspace.
However I don't think it's universally true this would always be bad. It depends on the speed at which you can insert and fetch blobs from your database, the speed of your NFS server, whether your database or file server can be replicated, and the problems with data integrity you're definitely going to have when not everything is known by the database.
No substitute for careful analysis of individual cases.
I can imagine that you want to hold these images in RAM to bypass the filesystem. Maybe MySQl did that for you, but that sounds like a heavy middleman.
If you'd have non-DB system for that (e.g. any game with a lot of tiny images), then you would generally want to use spritesheets, archives or some other approach to store many of these images in a single file instead of having each of them be a separate entry in the filesystem.
So if you want to store lots and lots of tiny objects, it's very inefficient to store them each in a separate file. At that point, you could design your own compound data format, making sure it efficiently supports all the different operations you might want to do... or you could just use a database which has already solved the problem for you.
It grinds my gears when people sneer at integers when they perform optimally or nearly optimally on the majority of use cases.
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.
In this case the security hole of /user/123 just needs to be properly locked down. That is all.
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.
I do know that I try to make my invoices to clients a bit more impressive, because I don't send many of them and they most definitely do notice if they get invoice #3 in May. Main problem is that in my country the rules for invoices are both murky and stringent, so I'm pretty much limited to a <year>0000<invoice no.> format.
> in my country the rules for invoices are both murky and stringent,
I sympathize, as the rules were like that in Italy, where I lived for a long time.
password reset email #124 gets url /user/124
password reset email #125 gets url /user/125 but that doesn't work because someone predicted it and got there before the requestor. no idea what account they'll get, but they'll get an account of some type.
This also comes up in shipping records. OK where do we go to steal an XYZ delivered today and sitting on a front porch? Well lets check
/shippinglabel/345
/shippinglabel/346
/shippinglabel/347 oh look delivered today, sitting on back porch step, and the address is right there
Another fun one is online financial documents with sequential accounts.
That's the nature of password reset link.
But a junior dev just out of code school doesn't necessarily think of this. So when I ask one to build the basic scaffold and db schema I say "make sure you use UUID," then later I show them how security holes like this can manifest.
I've seen this security hole so many times in other sites that I feel like it's a good first principle to limit "guess-ability" in the schema wherever possible.
IMO, explicitly prohibiting unauthorized access to an API endpoint is a basic security tenant, not a "holistic" one. if iterating through an API's integer key sequence results in unauthorized access to data, replacing the integers with UUIDs only masks the problem and I'd say is a classic example of how relying on obscurity for security can be a pernicious mistake, especially for a novice developer.
But sometimes you're working with a legacy API and/or a bad auth mechanism.
Not every project is greenfield or is maintained by senior devs.
You're misidentifying the actual security problem. Using URLs in this manner requires cryptographically secure random numbers, or my preferred method is to HMAC the URL to sign it's protected parameters. I actually wrote a small library for .NET called Clavis to demonstrate this idea [1]. The MAC acts as the cryptographically secure identifier needed to make the URL unguessable.
[1] https://higherlogics-trac.sourcerepo.com/higherlogics_clavis...
For example system-A gets id range for 1-1000, system-B gets id range for 1001-2000 and so on.
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.
The second generation of machines, where I did have a say in things, used UUID's and Postgres.
* New record is created, with new ID.
* Record is deleted.
* Database is shut down (cleanly).
* Database is started again.
* New record is created. It gets the old ID, because it's just looking at the current max(ID) or something like that.
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.
Once you get into application ids / timestamps / whatever you're no longer really using a natural key in my mind, you're just making your own algorithm for a surrogate key.
That sounds very similar to UUID version 1 except that you replaced MAC address with some other identifier and added a hashing step.
We can all invent algorithms for nice unique values when we include the caveat "apart from the edge cases". It's those edge cases that bugger everything up.
No, for a good primary key, the "unique" parts can be due to concatenation, but the whole key (and thus all the elements) needs to be unchanging. [0]
[0] Modern databases can actually deal with changeable PKs, though with a potentially serious performance hit, but in many use cases where data travels outside the database for some kind of interaction where the results need to get reentered into the database, this is still a problem, since you can't (in any general way) cascade updates to things (which may not even be online systems, e.g., paper records feeding human processes) outside of the DB.
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.
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.
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.
> UUIDs are for when you have more than one database server that people can write to, and those databases must not share IDs.
Exactly.
Which is fine for circumstances where a database round trip is acceptable at that point; there's situations where assigning IDs to items is something you want to have happen without database interaction at all.
That's basically the 'hello world' of web apps example where UUID's make sense already.
Either way, once I realized that I use my own little todo-app constantly, a server-based implementation became necessary. I'm working on a Horizon/RethinkDB version right now which uses UUID's by default. But if that were not the case I would've probably gone for simple incrementing ID's.
https://aphyr.com/posts/299-the-trouble-with-timestamps isn't bad.
UUIDs absolutely have their place - both the problems of distributed ID generation (mostly in remote clients / apps) and avoiding information leakage (-> German Tank Problem) are very real world examples where using them is a sensible thing.
However, my point was that using them as a default choice has a lot of drawbacks. All over our data model, we've got like 60-70 entities with serial IDs and about 2 with UUID columns.
Also, don't underestimate how fast PostgreSQL can deal you out from a sequence via nextval(), should you really go down that route. But yes, if the database is more than a millisecond away, UUIDs definitely have their time and place.
Agreed. UUID's are great for distributed/eventually consistent dbs. However, the article gives the primary reason the reason given to prefer UUID was specifically for key exhaustion.
Which, BIGINT is a much simpler solution.
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.
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.
If you're forking your DB (i.e. there will now be two copies that will diverge from each other) than sure, you would need to replicate your filesystem. But that's not complicated (copying directories is a solved problem).
If you're talking about replication for performance reasons, then DB replication and file replication aren't necessarily going to go hand-in-hand, and the solutions are going to look fairly different (for web apps, "file replication" probably looks like a CDN).
If you put your images behind a CDN, you solve 98% of that.. but you still have a thundering herd problem if your cache gets cold and a bunch of people want to see a non-CDNed iamge.
IMHO it is way way way easier to just throw the image up on google cloud storage (or s3), and store the URL in your database. Or better yet, derive the path from the ID so nothing to store. You can handle millions of hits per second with no DB load.
What if 100 people hit the page at the same time for an uncached image?
I am not saying it can't be done, just having trouble figuring out the benefits.. its easier to store an image in cloud storage over in a database... and cheaper.. and less bug prone...
LIMIT / OFFSET problems:
- There are portability issues with some other kinds of datastores and can be a mess to keep caches consistent (removing an item from one page requires invalidating every page set after it)
- It makes caching at the request / cdn level more complicated.
- Any update to the data while the user is paginating results in duplicate or missing items (especially noticeable with any kind of infinite scroll).
You can instead use something like ?object_id=123&page_size=50 to get the next 50 results after item with id=123 (assuming some default order, you could also pass in an order param). To keep your client code clean, you can return a pagination object with the response so you don't need the client to figure out the id of the last object and build the url.
I think this isn't a question of ints being a mistake and UUIDs being better, it's about using the most appropriate type based on requirements.
I'm legitimately not sure the author understands why large distributed systems utilise UIDs so just made up a reason that sounded good to them.
I haven't used a non-64 bit database in, let's say ten years, so making design decisions because a 32 bit int might be exhausted is rather dated advice at best.
I am struggling to think of a counterexample. Maybe chemical elements? Where the natural key is of course the atomic number, not the chemical symbol...
Before you suggest 'zipcodes' or 'states', consider that zipcodes are not in any sense natural, and anyway, like state codes, are so US-centric that they don't belong as top level elements in most real database schemas.
Beginners aren't making distributed systems on legacy 32 bit hardware (microcontrollers?) that max out a 32 bit space.
Really the article needs to bifurcate into
1) Actual mistakes beginners really make, like sucking entire tables over the network into local arrays and then hand writing a (slow and buggy) emulation of the DB server's JOIN. Bonus points for not memoizing/caching those giant tables. Another beginner comedy is NIH reinventing of the concept of having an index for speed, written entirely in slow application code. Turing complete being what it is, beginners sometimes try to write a rational database manager in their application code, not knowing if its really handy and could cut down on round trips and bandwidth, someone probably added that to the RDBMS code back in 1990. Another mistake beginners make is scaling, designing a 1E9 system for a 1E3 problem or vice versa. Another mistake beginners make is thinking some web post from '96 is relevant because nothing has changed since then (like storage and speed and application load) vs ignoring other posts from '96 because this time really nothing has changes since then (like basic computer science concepts), or rephrased 90% of the web should be ignored and beginners will ignore the wrong 90%.
2) Things beginners do that sounded like a great idea like not normalizing their data that turn into a 3-ring circus of writing convoluted application layer code to work around it. Sometimes you can write hundreds of LoC to avoid each line of SQL. Or getting into a habit of writing "baby's first todo app" using small 32 bit ints and then blowing it when they move up in the world into giant distributed systems.
That said, the default SERIAL type in postgres is 32-bit signed integer so it can be surprising when you exceed 2B items.
If you want to use uuids, uuid1 may be a better bet.
[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.
What they really MEAN is don't store images in your general purpose database, in particular as long strings.
There are however databases with first party support for image storage, which is useful because now you can store the image and metadata about the image together (as well re-using existing solutions like replication, authentication, etc).
Additionally their "solution" is bizarre, they've jumped from using a database to a paid service by a third party, which is itself backed by a database. That seems very "apples & oranges" to me, I mean it would obviously work, but is a big jump from in-house development using a general purpose database.
To give one specific example have they not heard about Oracle Multimedia? That's exactly what it is designed to offer.
It also keeps them coherent, if you store images on a filesystem but metadata in a database, since they don't share transactional contexts you will eventually end up in an inconsistent state ("dead" files without metadata, or live metadata missing the corresponding image data).
If you know the definition and the ins-and-outs of 'clustered index', 'index fragmentation' and 'page split' then feel free to use a UUID if you see fit-- otherwise, please don't.
If you are a beginner and for some reason have to have a UUID: Use a sequential, auto-incrementing integer for your clustered index column and make another second column that is your primary key that is your UUID.
This article feels like the blind leading the blind.
Can you enlighten us as to why? Seems to me if you're not at the scale where you see the benefits of UUIDs you're also not at the scale to see the drawbacks either (bigger, slower indices?).
Better more practical advice might be: if you don't need to use a X as a PK, don't use a X as a PK.
Where X can be either UUID or BIGINT.
If you don't expect your table to scale past a billion rows, INT is more than fine.
- No round trip for generating keys, data can be sent in with an existing or new uuid without having to hit the autonumber/keymaster, removes a single point of failure for a small fee on each row. Storage is cheap so 16-byte uuid is not a deal-breaker, the benefit is speed and horizontal scalability. Optimized read-only tables and/or caching can be made where this has any impact at all.
- Some databases like Oracle you need a sequence to even do autoumbering, huge pain
- Autonumbering is a pain when having to replicate across environments or when you start getting multiple databases and clusters
- Numeric ids for important data is not exposed, prevents easily incrementing for next/previous (other ways to do this but this is one)
- Many databases have a UUID field or field optimized for unique ids/guids/uuids now
- If you were a piece of data wouldn't you want to be unique? All your data are unique snowflakes with UUIDs. On a serious note, this can help to identify data across all types and not just in the same table.
Also, on "storage is cheap": http://www.sqlskills.com/blogs/kimberly/disk-space-is-cheap/
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 building but having a good idea of the data structure should be a "before thought" rather than a after-thought.
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.
My experience is that lots of people, including people with many more years of development on me, don't take this approach though.
Don't get me wrong. I don't roll an application into production until I've gone back and forth over both the interface and the database, until both satisfy me, the database is well-normalized, and so on. But when the first line of anything has yet to be written, I think I save a few iterations by first sketching out the fields and behavior of the user interface.
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.
Also, functional programming teaches to first think about the data structures and then about the processes/operations.
- storing their images externally, but forgetting to apply a consistent backup strategy to their image data
- Database is getting big, query returns lots of records, so just adding pagination. Forgetting that the user really doesn't actually want to page through results at all. The correct answer was probably to add proper faceted filters and full text search.
- using UUID keys everywhere, then making the mistake of mixing up UUID keys from one table with UUIDs from another one.
- Using nullable columns as a tool for schema change, but not deciding what it actually means for a particular column in a row to contain a NULL.
- Thinking that they will get away with just storing a chunk of structured data like an array in a particular column value because from the application point of view it's really just one blob of data anyway. In general I give a structured datatype like that two days before someone is writing a query that digs into the inner structure of it.
Consistency of results may be an issue, but any scheme you use is either going to show inconsistency when you move between pages, or it's going to lie to you. That's the reality of concurrent modification.
- 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 archivingAs opposed to?
None of the things mentioned are necessarily bad depending on context. For example, if I have a billion people per second visiting my website, yeah, storing the site images in PostgreSQL alongside the rest of the data probably isn't a great idea. But, if I am building an ERP system for the SMB market, it may be a fine idea given the degrees of concurrency I can expect, the cost of server equipment, and the advantages of keeping related data (binary or otherwise) together. The trade-offs are different. Simply put, it depends.
I think this post could be improved with some clarification and I do think a tip sheet for beginner's is a good idea coming from someone that does have good advice for PostgreSQL... it just needs to be less generally prescriptive and more instructive about how to think about the given advice.
If you fail to understand the meaning in data and just try to jam values into some persistence store to get through some transaction someone told you to code up, you'll likely make decisions for the future that you don't even know will come yet. Each table, each value, expresses some idea, some piece of information that, without even considering the application functionality, has meaning in the context of the rest of the data you're capturing. If you respect that relationship of ideas, you'll know whether or not normalization or de-normalization makes sense, you will more likely have a flexible information architecture rather than a brittle one.
So there's my beginner's advice: really understand what and why you are stuffing data into a database in the first place. Don't loose sight of the larger context. Conceptualize the information as information. Then figure out how the technology facilitates (or doesn't) the expression of that information with the greatest clarity.
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.
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)
Basically the whole thing could've been replaced by 5-10 tables. Maybe taking advantage of Postgres OORDBMS capabilities for the common columns.
Queries tended to group complex CTEs and multiple self-joins. You know it's an anti-pattern when devs start complaining about postgres join performance and you see they got two tables being joined 15 times...
http://instagram-engineering.tumblr.com/post/10853187575/sha...
As far as disk space... that could be a problem, but for most of the applications that developers work on, they never need to scale to the point where disk space is a problem. And if it does, you can always get more disk space.
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).
Even in your public web app, you also might have other back-end systems that cannot access S3, but can access your application services or DB (yikes). There are definitely many cases where external providers like S3 is the better approach, but not all. Also, you don't have a performance problem until you have a performance problem.
h/t to Wayne E. Seguin for his article http://www.starkandwayne.com/blog/uuid-primary-keys-in-postg...
Docs here: https://www.postgresql.org/docs/9.4/static/uuid-ossp.html
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?
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.
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.
Kind of ironic when you see how much bad press NoSQL databases got for missing join functionalities.
[0] http://www.joelonsoftware.com/articles/CollegeAdvice.html
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 not directly screen-by-screen design, one example I always see is that people default to creating an Address table. Many apps never store more than one address per user. If it's one-to-one, why force a join? (I think this is more of an overlap of both of our views though, and it popped into my head because Address tables are a pet-peeve)
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 according to your application, then making changes to the application can be difficult or require refactoring the database to match the new application specs. If you instead build your database to make sense standalone, it's up to each application to use it appropriately.
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.
Reminds me of the cynical saying for data warehouses, "Data in, but never out." :-)
What I have seen frequently enough is myopic data designs hurting flexibility later. For example - assuming 2 level customer relationships (Corporate parent and individual store) with things like regions appearing at tags, rather than flexible hierarchies. The assumptions behind this then gets built into the code base, and fixing it requires more than just a database update. (And even if you fix the database, you are missing the historical hierarchies)
This logic shouldn't be in the code base. A query should be isolated from the application logic, as I think we can all agree on. A change in the database should only require changing the query/procedure. Your business logic shouldn't be dependent on the internal workings of the query, just on it's input/output which shouldn't need changing. Adding a feature that requires a database refactor shouldn't impact the internal logic of another feature (unless it's intentional).
I'm not encouraging willy-nilly design. I'm not saying "stick everything on one row". Just don't design a one-to-one as a one-to-many just because it might theoretically change. However, you should still have the foresight to put yourself in a position where that change is easy. People seem to think that "refactoring" is a dirty word. I'm reasonably confident none of us have worked on an application that has never been refactored. Plan on those potential refactors, not convince yourself that "this is how proper design works". I find THAT is what inevitably leads to the painful refactors.
> And even if you fix the database, you are missing the historical hierarchies
I'm not following this one. Are you referring to an audit trail?
All I mean is that a database should make sense 100% on its own. Your database schema should represent what makes sense for your data, not what your application wants to see. I've never seen a case where designing the database first results in a poorly optimized application codebase. If your database-first design results in terrible access patterns for applications, then your database just wasn't properly designed in the first place. Your data should have a sensible structure on its own without catering specifically to application specs. If your structure really is sensible, no application should have a difficult time manipulating the data within it.
I'm not following this one. Are you referring to an audit trail?
No - meaning if update the database schema, data will be missing that wasn't collected properly the first time. (If you didn't think you needed customer hierarchies, you didn't create them as customers came in)
I hear you on avoiding over-generalizing. That creates problems too.
Maybe I'm nitpicking but even if you had that field in the database, it's still up to the application to collect it (unless it's something like a timestamp, but that's just a dumb mistake regardless of how you design your database)
The idea that an entire database should be designed and normalized purely to allow people with no knowledge of basic database mechanics to work with it shocks me. Instead of designing disgusting database structures, send your marketing analytics people to courses where they can learn the basics. I had one job in particular where I spent more than a year dealing with this crap, and it was the most frustrating thing I've ever had to deal with. Never again.
When you're not at some massively huge scale, this advice is mostly terrible. You should do exactly the opposite.
[0] http://rob.conery.io/2014/05/29/a-better-id-generator-for-po...
It allows more configurability as well - you could have a UI to control/audit all such values and allow for easy external mapping.
One example: counter-example to #4. Oracle database (since 11g) has a "fast add column" feature - which allows adding a non-NULL column with a default value to an arbitrarily-large existing table, without a "rewrite" of the table. (Behind the scenes, the default value is stored as metadata - and the default value is then read from the metadata for preexisting rows, rather than updating each and every row in the table.)
>>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.
innodb_file_per_table 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.