6 ms·

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 import

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.
- BoiledCabbage 3y ago
  So let's continue with your example. I'm still trying to understand your position. The phrase "Carbon is a chemical element with symbol C and atomic number 6" specifies a "chemical element". That to me is a category, do you disagree? Have you not just categorized Carbon as being a chemical element? Or moving away from things you disagree with, how would you specifically model the statement above in a physical table / DB. Or the simpler "Carbon is a chemical element with symbol C"? Do you have a table with a single column that stores the string above? Do you have a table with 2 columns one that stores the string "Carbon" one that stores, "Chemical Element" and one that stores "C"? Do you instead have a table with 2 foreign keys one tracking names of elements (ElementId, Name) that would store (1, "Carbon"); (2, "Nitrogen") depending on the order you created your statements? And another table storing element abbreviations with entity Ids and fields of "C", "Fe", "Ni"? And name your statement table chemical elements? Or do you add a third column and call it relations and have a list of relation types where "IsAChemicalElement" is one type of relation? The phrase statement to you have a very specific meaning to you in both an English everyday use sense, and a modeling sense. We both agree (or mostly agree) on what statement means in an everyday use sense, but it can mean many different things in a modeling sense. Can you specific how in a physical table you would choose to model the statement "Carbon is a chemical element with symbol C"? and the presumably related statement "Carbon has Atomic Number 6" if helpful in explaining. What I'm getting from you now is you're envisioning something closer to a Entity Component System but for general data modeling. Or more accurately something like prolog style relations; with all entities, regardless of "type", tracked in a single table of entity ids, and having each relation between entities referred to as a statement, and tracked in their own table. But it's still not clear to me until you give an example of how you would model it.
  - feoren 3y ago
    > The phrase "Carbon is a chemical element with symbol C and atomic number 6" specifies a "chemical element". Have you not just categorized Carbon as being a chemical element? A fair point -- except it's a worldwide consortium of scientists that made that categorization, so it's not like I introduced it here. But you're right: the categorization "carbon is a chemical element" is not actually what I care about; I care about the fact that "Carbon is one of the things you might see in a Chemical Formula, where it will be represented by a C." It's possible that things that aren't chemical elements, like Amino Acids, could also have these same statements made about them. So as you see, I've not actually restricted members of my table to only Chemical Elements (with this minor change). > Do you have a table with a single column that stores the string above? Do you have a table with 2 columns ... Everything you listed is a possibility, except you need GUIDs or a similar scheme since the same entity is represented in multiple tables. In general it's just (key, statement) where "statement" is all the fields you need to exactly make your statement. I'd probably put the string "Carbon" in its own table, possibly with other text used to canonically identify the entity in English (or I'd explicitly store the language if I was going full internationalization). I also sometimes track singular vs. plural, although for chemical elements that's I'd put atomic number and the chemical-formula abbreviation in the same table, possibly with other "periodic table" type information if it were important. I probably would stop there: I wouldn't actually say it's a solid, for instance -- that's just not an actually useful statement in the real world. This is just too simple an example to get much meat out of it. But that's OK - small tables are fine. There are no foreign key constraints because there is no single table which is a canonical list of "chemical elements", by design. Foreign keys are secretly business rules that just happen to be easily representable in a database. Business rules go in the application layer. > [statement] can mean many different things in a modeling sense A statement (or component, spoiler that you got that right) is an immutable ("honorarily" immutable in the DB, but truly immutable in the application layer) ordered tuple of related data (read: struct) that can be associated with an entity. Of course to be stored in the DB it must be associated with an entity, but in the application layer it's useful that they don't have to be. > What I'm getting from you now is you're envisioning something closer to a Entity Component System but for general data modeling. Yes, that is the term I use for it: "Entity-Component Architecture". I've taken to calling them "statements" instead of "components" when introducing the topic because it's more obvious to people who don't know ECS, and people who do know ECS assume I'm just talking about games. The word "system" shows up a lot but does not have a specific meaning; it's more like "service". Basically systems are code elements that layer on successively more rules about what operations are or are not valid to perform on sets of these (key, statement) objects -- but that's just one of many useful words to describe such code elements. > Or more accurately something like prolog style relations; with all entities, regardless of "type", tracked in a single table of entity ids, and having each relation between entities referred to as a statement, and tracked in their own table. You don't actually need a master list of every entity, and there are even reasons to avoid it. But you could do it that way if you wanted; it has its pros and cons. I personally don't. Keep in mind not all statements are "relations between entities". Some are; some are relations between 3+ entities. Some are just plain-old facts that you want to remember. But yes, each different kind of statement you want to make gets its own table. > But it's still not clear to me until you give an example of how you would model it. You have the right idea. Frankly the differences don't show up until a pretty reasonable level of complexity, and everything is dependent on the exact business cases you want to support. I really should just write a blog post on it.

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.

BoiledCabbage 3y ago
So let's continue with your example. I'm still trying to understand your position. The phrase "Carbon is a chemical element with symbol C and atomic number 6" specifies a "chemical element". That to me is a category, do you disagree? Have you not just categorized Carbon as being a chemical element? Or moving away from things you disagree with, how would you specifically model the statement above in a physical table / DB. Or the simpler "Carbon is a chemical element with symbol C"? Do you have a table with a single column that stores the string above? Do you have a table with 2 columns one that stores the string "Carbon" one that stores, "Chemical Element" and one that stores "C"? Do you instead have a table with 2 foreign keys one tracking names of elements (ElementId, Name) that would store (1, "Carbon"); (2, "Nitrogen") depending on the order you created your statements? And another table storing element abbreviations with entity Ids and fields of "C", "Fe", "Ni"? And name your statement table chemical elements? Or do you add a third column and call it relations and have a list of relation types where "IsAChemicalElement" is one type of relation? The phrase statement to you have a very specific meaning to you in both an English everyday use sense, and a modeling sense. We both agree (or mostly agree) on what statement means in an everyday use sense, but it can mean many different things in a modeling sense. Can you specific how in a physical table you would choose to model the statement "Carbon is a chemical element with symbol C"? and the presumably related statement "Carbon has Atomic Number 6" if helpful in explaining. What I'm getting from you now is you're envisioning something closer to a Entity Component System but for general data modeling. Or more accurately something like prolog style relations; with all entities, regardless of "type", tracked in a single table of entity ids, and having each relation between entities referred to as a statement, and tracked in their own table. But it's still not clear to me until you give an example of how you would model it.
- feoren 3y ago
  > The phrase "Carbon is a chemical element with symbol C and atomic number 6" specifies a "chemical element". Have you not just categorized Carbon as being a chemical element? A fair point -- except it's a worldwide consortium of scientists that made that categorization, so it's not like I introduced it here. But you're right: the categorization "carbon is a chemical element" is not actually what I care about; I care about the fact that "Carbon is one of the things you might see in a Chemical Formula, where it will be represented by a C." It's possible that things that aren't chemical elements, like Amino Acids, could also have these same statements made about them. So as you see, I've not actually restricted members of my table to only Chemical Elements (with this minor change). > Do you have a table with a single column that stores the string above? Do you have a table with 2 columns ... Everything you listed is a possibility, except you need GUIDs or a similar scheme since the same entity is represented in multiple tables. In general it's just (key, statement) where "statement" is all the fields you need to exactly make your statement. I'd probably put the string "Carbon" in its own table, possibly with other text used to canonically identify the entity in English (or I'd explicitly store the language if I was going full internationalization). I also sometimes track singular vs. plural, although for chemical elements that's I'd put atomic number and the chemical-formula abbreviation in the same table, possibly with other "periodic table" type information if it were important. I probably would stop there: I wouldn't actually say it's a solid, for instance -- that's just not an actually useful statement in the real world. This is just too simple an example to get much meat out of it. But that's OK - small tables are fine. There are no foreign key constraints because there is no single table which is a canonical list of "chemical elements", by design. Foreign keys are secretly business rules that just happen to be easily representable in a database. Business rules go in the application layer. > [statement] can mean many different things in a modeling sense A statement (or component, spoiler that you got that right) is an immutable ("honorarily" immutable in the DB, but truly immutable in the application layer) ordered tuple of related data (read: struct) that can be associated with an entity. Of course to be stored in the DB it must be associated with an entity, but in the application layer it's useful that they don't have to be. > What I'm getting from you now is you're envisioning something closer to a Entity Component System but for general data modeling. Yes, that is the term I use for it: "Entity-Component Architecture". I've taken to calling them "statements" instead of "components" when introducing the topic because it's more obvious to people who don't know ECS, and people who do know ECS assume I'm just talking about games. The word "system" shows up a lot but does not have a specific meaning; it's more like "service". Basically systems are code elements that layer on successively more rules about what operations are or are not valid to perform on sets of these (key, statement) objects -- but that's just one of many useful words to describe such code elements. > Or more accurately something like prolog style relations; with all entities, regardless of "type", tracked in a single table of entity ids, and having each relation between entities referred to as a statement, and tracked in their own table. You don't actually need a master list of every entity, and there are even reasons to avoid it. But you could do it that way if you wanted; it has its pros and cons. I personally don't. Keep in mind not all statements are "relations between entities". Some are; some are relations between 3+ entities. Some are just plain-old facts that you want to remember. But yes, each different kind of statement you want to make gets its own table. > But it's still not clear to me until you give an example of how you would model it. You have the right idea. Frankly the differences don't show up until a pretty reasonable level of complexity, and everything is dependent on the exact business cases you want to support. I really should just write a blog post on it.