3 ms·
This is an interesting idea, because I haven't run across this paradigm before. Can you please flesh it out a little bit further, so I can better understand it?
by eszed 3y ago
This is an interesting idea, because I haven't run across this paradigm before. Can you please flesh it out a little bit further, so I can better understand it?
So, I build a table (call it "transactions"?) that contains all of these, without repeating any of the similar fields. (I'll note that that's still categorizing data; I'm not sure what that implies about your original point.) There will, of necessity, be fields that are different across the two transaction, um, types. How do you handle those?
What happens when your unified table gets so big that it affects performance? It seems to me that separating the two types of transaction into separate tables at least postpones that day.
What about presenting different types of data to end-users? Business logic dictates that accountants and analysts will want to treat these types of transaction differently.
How easy is this design to work with and reason about? I'd fear it comes at the cost of monster queries which align poorly with business logic. I'd be worried that makes said analysts dependent on the db architect to construct their queries for them, which isn't a situation I'd prefer. Is that, in practice, not a concern?
Which of those real-world concerns am I over-valuing?
- feoren 3y agoI probably should sit down and write a comprehensive blog post about it sometime instead of dropping pieces of it in HN comments. > So, I build a table (call it "transactions"?) that contains all of these, without repeating any of the similar fields. That's still kinda just re-categorizing things. It's more like: identify the groups of fields that PurchaseOrders and SalesOrders have in common, and make that group a table (or possibly more than one). > How easy is this design to work with and reason about? I'd be worried that makes said analysts dependent on the db architect to construct their queries for them, which isn't a situation I'd prefer. Is that, in practice, not a concern? It's much easier to work with and reason about long-term for software developers, in my opinion. It's much harder to work directly in the database, which is a deal-breaker for some. Analysts are not constructing their own queries without help from a lot of views; but you have more opportunity to custom-tailor those views to their specific needs. Frankly, I don't think business analysts should be directly hitting the database -- it is a persistence layer, not an application layer. It'd be like demanding that analysts can specific memory addresses -- it's just the wrong layer of abstraction for them. "Canonical" functionality is added piecemeal in layers by various domain "library"-like layers. Analysis can pick the libraries that they need (more likely, devs set this up for them). > I'd fear it comes at the cost of monster queries which align poorly with business logic. Yes, queries are bigger and include more JOINs, but they are more aligned with the business logic, not less. I've done it with table sizes on the order of 100 million records (SQL Server), and it's just fine, but I haven't gotten into the real "big data" sizes. Databases are very good at joins with proper indexes nowadays. If you're in "big data" size, I don't see a reason why table-per-noun is any better of a starting point for Apache Spark or whatever you're doing. > Which of those real-world concerns am I over-valuing? In my opinion, you are over-valuing direct access to the database tables with no business logic applied on top at the application layer. That's what the application layer is for.
- eszed 3y ago> I probably should sit down and write a comprehensive blog post about it sometime Please do! I enjoy reading new approaches to apparently "settled" fields. I'm not sure I agree with you yet, or (properly speaking) quite understand what you're doing, but I'd be fascinated to hear more.