16 ms·

Maybe I'm misunderstanding the use of "category" or "thing" here, but it would be difficult for me to make useful business software for my clients if I didn't h

Maybe I'm misunderstanding the use of "category" or "thing" here, but it would be difficult for me to make useful business software for my clients if I didn't have one table for "sales orders" and another for "purchase orders".

feoren 3y ago
No, it wouldn't. They have a lot in common. What various statements do you want to make about "sales orders"? What various statements do you want to make about "purchase orders"? My guess is that a lot of those statements are the same. In fact, a lot of the statements, e.g. "when was this record last modified?", are statements you want to make about all sorts of things. If you list out everything you might ever want to know about a PurchaseOrder, and everything you might ever want to know about a SalesOrder, and you just fucking JAM all that shit in each table (or a combinatorial explosion of child and association tables), you end up with two giant tables that are kinda-the-same-but-kinda different; you end up with a bad database. A database with a table called SalesOrder(s) and another called PurchaseOrder(s) is a bad database. That is exactly my point. Everybody makes this mistake. They only get away with it because they're tackling simple, easy problems (badly). Everyone seems to think that one record is one noun. No! One record is one statement. One table is not a list of instances of one distinct noun; it's a list of instances of one kind of statement you want to make. This is much closer to a "pure" relational model (e.g. as espoused by Codd, as hinted at in Out of the Tar Pit) than constantly making the exact categories-are-hard-boundaries mistake discussed in TFA. Most people have started recognizing the problems with "class Cat : class Animal", but then they do this exact thing in their databases, reintroducing all the same problems.
- BoiledCabbage 3y ago
  Yes Out of the Tar Pit is wonderful, but I don't believe it actually argues what you appear to be claiming it argues. It is arguing a different (and more important point). Or put more specifically, one could argue that a SalesOrder and a PurchaseOrder are the same thing, because fundamentally they are with a different perspective. But one cannot argue that a Teacher and a Student are the same thing. As a result having a table for Teachers and a separate table for Students is a good thing. And that would be categorization. Are you arguing that Students and Teachers should be stored in the same table when modeling a DB? If so, then you are arguing for categorization. But even then, that's not the heat of the issue. More detailed there are 3 arguments that in my opinion you are muddling together: 1. Some things are the same thing but viewed from the perspective of two different users, these should be modeled as the same thing (PurchaseOrder vs SalesOrder). [I agree with this specific case, but there aren't a ton of these] 2. You should model events in a system and not nouns/entities [The Out of the Tarpit argument, which I also agree with and is the direction of ideal coding if performance were no object, and you can easily do queries / derived models on top of it.] 3. You shouldn't categorize things. [This argument I don't understand as I see generalization and categorization as the essence of software modeling, but am open to an explanation. A StudentEnrolled event table should be categorized separately from a ClassLectureGiven event table, and those are categories.]
  - feoren 3y ago
    > one could argue that a SalesOrder and a PurchaseOrder are the same thing, I am not arguing that. > As a result having a table for Teachers and a separate table for Students is a good thing. And that would be categorization. I disagree that that's a good thing. > Are you arguing that Students and Teachers should be stored in the same table when modeling a DB? If so, then you are arguing for categorization. No. I am arguing that you do not know beforehand how many tables Students and Teachers (nor Purchase/Sales Orders) are going to take up, because you are going to come up with more things to say about them. The more design attention you give it, the more tables they're likely to take up. And those tables are very likely to be shared with other entities. Because once you start making statements like "This record was modified by User X" you are no longer even speaking the language of Students and Teachers. > 1. Some things are the same thing but viewed from the perspective of two different users, these should be modeled as the same thing No, I am not claiming that. I never said "same thing". > 2. You should model events in a system and not nouns/entities No, I didn't say "events", I said "statements". An event is "X happened at Y time" -- which also happens to be a statement. But another statement is "Carbon is a chemical element with symbol C, atomic number 6, and standard atomic weight of 12.011". You could argue that's actually 3 statements, and that decision is a complex engineering decision you make based on the tradeoffs in front of you. I'd actually probably break it down into "Carbon is a chemical element with symbol C and atomic number 6", since those are relatively unique to Chemical Elements, and take its atomic weight and some other properties to a different table since those properties are shared more widely. Modeling events accidentally tends to get databases closer to the design I advocate for, which is "one table per set of data fields you need to make a clear statement". But that's just a happy coincidence. I claim the success of event-based architectures is mostly coincidental to their "eventness" and actually more to do with the fact that they're closer to a "statement-driven" rather than "noun-driven" architecture. You can have a statement-driven architecture without events at all. In fact it would be silly to try to model the statement that Carbon's Atomic Number is 6 as an event. Plenty of things are just "facts" (for purposes of your system) that you want to state without having to say when this fact became true. > 3. You shouldn't categorize things. Correct. In your defense, I am trying to jam a big argument with a decade of theory behind it into an HN comment, so I don't really fault you for not getting my point. But I'm saying a good database design is about 90 degrees rotated to the standard "one table per noun" design. You don't put your Students and Teachers in the same table, nor in different tables: you don't put them in any table. You only put statements in tables. Some of those statements are about Students. Some of them are about Teachers. Some of your Students are also Teachers! (Ever seen a class TA'd by a Ph.D. student?) Some of your statements about Students will still be true when they stop being Students, and some won't! You make a new table when you need to make a new kind of statement about anything, regardless of what noun or category they're about. You basically never need to worry about what an entity "is" or "is not". You literally never need to categorize anything.
- 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 ago
    I 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.
- TehShrike 3y ago
  Positive quantities on sales orders decrease inventory. Positive quantities on purchase orders increase inventory. Closed sales orders increase cost of goods sold and accounts receivable. Closed purchase orders increase assets and accounts payable. You could create one uber-record that could represent all of these different assets, but it wouldn't necessarily be a good idea, because my clients and their accountants conceive of them as separate entities, and I need to make them legible to my clients as separate entities. I create schemas that store/make legible data beyond a double-entry general ledger.

feoren 3y ago

No, it wouldn't. They have a lot in common. What various statements do you want to make about "sales orders"? What various statements do you want to make about "purchase orders"? My guess is that a lot of those statements are the same. In fact, a lot of the statements, e.g. "when was this record last modified?", are statements you want to make about all sorts of things. If you list out everything you might ever want to know about a PurchaseOrder, and everything you might ever want to know about a SalesOrder, and you just fucking JAM all that shit in each table (or a combinatorial explosion of child and association tables), you end up with two giant tables that are kinda-the-same-but-kinda different; you end up with a bad database. A database with a table called SalesOrder(s) and another called PurchaseOrder(s) is a bad database. That is exactly my point. Everybody makes this mistake. They only get away with it because they're tackling simple, easy problems (badly). Everyone seems to think that one record is one noun. No! One record is one statement. One table is not a list of instances of one distinct noun; it's a list of instances of one kind of statement you want to make. This is much closer to a "pure" relational model (e.g. as espoused by Codd, as hinted at in Out of the Tar Pit) than constantly making the exact categories-are-hard-boundaries mistake discussed in TFA. Most people have started recognizing the problems with "class Cat : class Animal", but then they do this exact thing in their databases, reintroducing all the same problems.

BoiledCabbage 3y ago
Yes Out of the Tar Pit is wonderful, but I don't believe it actually argues what you appear to be claiming it argues. It is arguing a different (and more important point). Or put more specifically, one could argue that a SalesOrder and a PurchaseOrder are the same thing, because fundamentally they are with a different perspective. But one cannot argue that a Teacher and a Student are the same thing. As a result having a table for Teachers and a separate table for Students is a good thing. And that would be categorization. Are you arguing that Students and Teachers should be stored in the same table when modeling a DB? If so, then you are arguing for categorization. But even then, that's not the heat of the issue. More detailed there are 3 arguments that in my opinion you are muddling together: 1. Some things are the same thing but viewed from the perspective of two different users, these should be modeled as the same thing (PurchaseOrder vs SalesOrder). [I agree with this specific case, but there aren't a ton of these] 2. You should model events in a system and not nouns/entities [The Out of the Tarpit argument, which I also agree with and is the direction of ideal coding if performance were no object, and you can easily do queries / derived models on top of it.] 3. You shouldn't categorize things. [This argument I don't understand as I see generalization and categorization as the essence of software modeling, but am open to an explanation. A StudentEnrolled event table should be categorized separately from a ClassLectureGiven event table, and those are categories.]
- feoren 3y ago
  > one could argue that a SalesOrder and a PurchaseOrder are the same thing, I am not arguing that. > As a result having a table for Teachers and a separate table for Students is a good thing. And that would be categorization. I disagree that that's a good thing. > Are you arguing that Students and Teachers should be stored in the same table when modeling a DB? If so, then you are arguing for categorization. No. I am arguing that you do not know beforehand how many tables Students and Teachers (nor Purchase/Sales Orders) are going to take up, because you are going to come up with more things to say about them. The more design attention you give it, the more tables they're likely to take up. And those tables are very likely to be shared with other entities. Because once you start making statements like "This record was modified by User X" you are no longer even speaking the language of Students and Teachers. > 1. Some things are the same thing but viewed from the perspective of two different users, these should be modeled as the same thing No, I am not claiming that. I never said "same thing". > 2. You should model events in a system and not nouns/entities No, I didn't say "events", I said "statements". An event is "X happened at Y time" -- which also happens to be a statement. But another statement is "Carbon is a chemical element with symbol C, atomic number 6, and standard atomic weight of 12.011". You could argue that's actually 3 statements, and that decision is a complex engineering decision you make based on the tradeoffs in front of you. I'd actually probably break it down into "Carbon is a chemical element with symbol C and atomic number 6", since those are relatively unique to Chemical Elements, and take its atomic weight and some other properties to a different table since those properties are shared more widely. Modeling events accidentally tends to get databases closer to the design I advocate for, which is "one table per set of data fields you need to make a clear statement". But that's just a happy coincidence. I claim the success of event-based architectures is mostly coincidental to their "eventness" and actually more to do with the fact that they're closer to a "statement-driven" rather than "noun-driven" architecture. You can have a statement-driven architecture without events at all. In fact it would be silly to try to model the statement that Carbon's Atomic Number is 6 as an event. Plenty of things are just "facts" (for purposes of your system) that you want to state without having to say when this fact became true. > 3. You shouldn't categorize things. Correct. In your defense, I am trying to jam a big argument with a decade of theory behind it into an HN comment, so I don't really fault you for not getting my point. But I'm saying a good database design is about 90 degrees rotated to the standard "one table per noun" design. You don't put your Students and Teachers in the same table, nor in different tables: you don't put them in any table. You only put statements in tables. Some of those statements are about Students. Some of them are about Teachers. Some of your Students are also Teachers! (Ever seen a class TA'd by a Ph.D. student?) Some of your statements about Students will still be true when they stop being Students, and some won't! You make a new table when you need to make a new kind of statement about anything, regardless of what noun or category they're about. You basically never need to worry about what an entity "is" or "is not". You literally never need to categorize anything.
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 ago
  I 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.
TehShrike 3y ago
Positive quantities on sales orders decrease inventory. Positive quantities on purchase orders increase inventory. Closed sales orders increase cost of goods sold and accounts receivable. Closed purchase orders increase assets and accounts payable. You could create one uber-record that could represent all of these different assets, but it wouldn't necessarily be a good idea, because my clients and their accountants conceive of them as separate entities, and I need to make them legible to my clients as separate entities. I create schemas that store/make legible data beyond a double-entry general ledger.