5 ms·

Normalised designs invariably have pretty narrow tables. In most BCNF schemas most tables will end up with 3 or 4 columns at most. Additionally in the situati

Normalised designs invariably have pretty narrow tables. In most BCNF schemas most tables will end up with 3 or 4 columns at most. Additionally in the situation where you have a lot of non-prime columns you always have the option of decomposing the table into multiple tables sharing the same primary key. Useful if some columns are large but rarely used.

MustardTiger 10y ago
What are you talking about? A table has as many columns as the thing it describes has attributes to be described. You can have a fully normalized database with 50 column tables in it, and there's nothing unusual or suspect about that.
- calpaterson 10y ago
  No, relations describe facts, not things. Heath's theorem means you can decompose a table/relation that has n non-key attributes into n tables. This is something you have to do for BCNF. Theoretically it's possible to imagine a normalised relation with 50 columns. In practice a table like that is unusual and suspect (frankly, even in a denormalised design).
  - sgeneris 10y ago
    There NO relationships whatsoever between normalization and the number of attributes, which are determined by the number of properties that entities have in the real world i.e., the conceptual model.
    - calpaterson 10y ago
      That is not in conflict with what I said
      - sgeneris 10y ago
        No. I just thought the clarification was important.
  - MustardTiger 10y ago
    >No, relations describe facts, not things. That is impressively pointless pedantry even for HN, well done. >Theoretically it's possible to imagine a normalised relation with 50 columns Yes, that would be what I said. "You're wrong because exactly what you said is correct!"
    - sgeneris 10y ago
      No, correctness is not pedantry, except for the ignorant. The database facts are axioms and the facts in query results are theorems -- logical inferences from the axioms. If you don't understand that you have no clue what a relational database is and what a RDBMS does. But hey, you're in the majority. Any linking of number of columns to normalization is pure nonsense. The only accurate thing that can be said is that if you split a relation into multiple projections, by definition each will have less attributes than the original relation. But this is a triviality and it says nothing about the number of columns in the projected relations.
      - MustardTiger 10y ago
        >No, correctness is not pedantry Pedantry is though. And that's what that entirely pointless post was. >Any linking of number of columns to normalization is pure nonsense. That's what I said, that's the point. Don't jump in to a thread you can't be bothered to read.
        sgeneris 10y ago
        Pointless to anybody incapable of getting the point, of which there are too many.
- sgeneris 10y ago
  Tables are not relations. Relations have attributes, not columns, that represent entity properties. A R-table is a visual shorthand for a relation -- a way to visualize it on some physical medium (paper, screen) and the two should not be confused.
sgeneris 10y ago
Normalization is not the same as full normalization. Full normalization does not decompose into relations with same key, because that means splitting one type of entities in two, that makes no logical sense. Rather, it splits non-5NF relations that represent facts of multiple types into 5NF relations that represent facts of a single type and they have different keys.
rco8786 10y ago
> the option of decomposing the table into multiple tables sharing the same primary key sounds suspiciously like denormalization
- sgeneris 10y ago
  First, you got it backwards: denormalization joins relations, normalization splits them; Second, if they share the same key, then it's not normalization, as the the split relations have distinct keys.
  - rco8786 10y ago
    In most cases that ends up being true but that's not the definition of either of those terms. Normalization is the process of organizing the attributes and relations to, among other things, eliminate redundancy. 2 relations that store and share the same primary key is by definition redundant. Splitting relations for the sake of skinnier tables is creating redundancy(the primary key is stored twice) and insert/update anomalies and can be reasonably thought of as a denormalizing behavior.

MustardTiger 10y ago

What are you talking about? A table has as many columns as the thing it describes has attributes to be described. You can have a fully normalized database with 50 column tables in it, and there's nothing unusual or suspect about that.

calpaterson 10y ago
No, relations describe facts, not things. Heath's theorem means you can decompose a table/relation that has n non-key attributes into n tables. This is something you have to do for BCNF. Theoretically it's possible to imagine a normalised relation with 50 columns. In practice a table like that is unusual and suspect (frankly, even in a denormalised design).
- sgeneris 10y ago
  There NO relationships whatsoever between normalization and the number of attributes, which are determined by the number of properties that entities have in the real world i.e., the conceptual model.
  - calpaterson 10y ago
    That is not in conflict with what I said
    - sgeneris 10y ago
      No. I just thought the clarification was important.
- MustardTiger 10y ago
  >No, relations describe facts, not things. That is impressively pointless pedantry even for HN, well done. >Theoretically it's possible to imagine a normalised relation with 50 columns Yes, that would be what I said. "You're wrong because exactly what you said is correct!"
  - sgeneris 10y ago
    No, correctness is not pedantry, except for the ignorant. The database facts are axioms and the facts in query results are theorems -- logical inferences from the axioms. If you don't understand that you have no clue what a relational database is and what a RDBMS does. But hey, you're in the majority. Any linking of number of columns to normalization is pure nonsense. The only accurate thing that can be said is that if you split a relation into multiple projections, by definition each will have less attributes than the original relation. But this is a triviality and it says nothing about the number of columns in the projected relations.
    - MustardTiger 10y ago
      >No, correctness is not pedantry Pedantry is though. And that's what that entirely pointless post was. >Any linking of number of columns to normalization is pure nonsense. That's what I said, that's the point. Don't jump in to a thread you can't be bothered to read.
      - sgeneris 10y ago
        Pointless to anybody incapable of getting the point, of which there are too many.
sgeneris 10y ago
Tables are not relations. Relations have attributes, not columns, that represent entity properties. A R-table is a visual shorthand for a relation -- a way to visualize it on some physical medium (paper, screen) and the two should not be confused.

sgeneris 10y ago

Normalization is not the same as full normalization. Full normalization does not decompose into relations with same key, because that means splitting one type of entities in two, that makes no logical sense. Rather, it splits non-5NF relations that represent facts of multiple types into 5NF relations that represent facts of a single type and they have different keys.

rco8786 10y ago

> the option of decomposing the table into multiple tables sharing the same primary key sounds suspiciously like denormalization

sgeneris 10y ago
First, you got it backwards: denormalization joins relations, normalization splits them; Second, if they share the same key, then it's not normalization, as the the split relations have distinct keys.
- rco8786 10y ago
  In most cases that ends up being true but that's not the definition of either of those terms. Normalization is the process of organizing the attributes and relations to, among other things, eliminate redundancy. 2 relations that store and share the same primary key is by definition redundant. Splitting relations for the sake of skinnier tables is creating redundancy(the primary key is stored twice) and insert/update anomalies and can be reasonably thought of as a denormalizing behavior.