4 ms·

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 datab

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

lloyd-christmas 10y ago
> The assumptions behind this then gets built into the code base 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?
- developer2 10y ago
  Nice reply, first time I've wished to upvote more than once. The context you provide is something I don't think of enough when I am presenting my view. It could be misinterpreted that I'm advocating designing databases that are designed in some horribly archaic structure that is difficult for applications to work with. 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.
- mathattack 10y ago
  > 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? 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.
  - lloyd-christmas 10y ago
    > data will be missing that wasn't collected properly the first time 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)
    - mathattack 10y ago
      Yes - I think we both agree.
developer2 10y ago
Data warehousing is something I really hate to even look at in general. The implementations are typically optimized not for query performance; instead, they are "designed" to allow people who don't even know what a database is to execute queries. 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.

lloyd-christmas 10y ago

> The assumptions behind this then gets built into the code base 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?

developer2 10y ago
Nice reply, first time I've wished to upvote more than once. The context you provide is something I don't think of enough when I am presenting my view. It could be misinterpreted that I'm advocating designing databases that are designed in some horribly archaic structure that is difficult for applications to work with. 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.
mathattack 10y ago
> 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? 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.
- lloyd-christmas 10y ago
  > data will be missing that wasn't collected properly the first time 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)
  - mathattack 10y ago
    Yes - I think we both agree.

developer2 10y ago

Data warehousing is something I really hate to even look at in general. The implementations are typically optimized not for query performance; instead, they are "designed" to allow people who don't even know what a database is to execute queries. 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.