6 ms·

I think he contradicts himself and confuses the data with the information. I don't think delimited values representing a table in a column can be considered nor

by gfody 5y ago

I think he contradicts himself and confuses the data with the information. I don't think delimited values representing a table in a column can be considered normalized. >Make no mistake, encoding multiple values in a single string is generally a bad design. But it has nothing to do with first normal form. First normal form mean a column should not allow relations as values. A comma-separated string is still just a single string from the perspective of the database type system.

mamcx 5y ago
No, is not a contradiction. Is like say "Is wrong to store a large json in a cell in an array, that make it not an array". A relation is A VALUE. That is. The atomicity of the data INSIDE each "cell" is orthogonal to the fact the that relation is a relation. This is the point.
- blacktriangle 5y ago
  For even more fun mind bending, there's absolutely nothing that violates the relational model in having relation-valued attributes. SQL doesn't support that, but that doesn't mean its non-relational.
  - derefr 5y ago
    Re: DB support for relations as values — while no DB I know of supports full relations (sets of tuples) in values, composite-typed (single-tuple) columns are a thing, e.g. the on_hand example table in https://www.postgresql.org/docs/current/rowtypes.html https://www.postgresql.org/docs/current/rowtypes.html Also, presuming DB support for some format like JSON, you can encode a relation into a JSON value, stick it into a column and then, at query time, use a function like Postgres's json_to_recordset(json) to emit a relation (set of tuples). Postgres doesn't know how to deal with it "as is" (i.e. it forces you to treat the result of a function as a named joined table in your query, rather than treating it as an concrete value) but it does understand where it is in relation (heh) to the rest of the row, since you can unnest() values from this virtual table and get the right results out. As awful as that sounds, we're actually doing that in production to normalize data coming from redis_fdw (which gives you, basically, (key text, value jsonb) rows for Redis hashes) into relational shape.
  - fuckf4ce 5y ago
    Relation valued attributes are precisely the antithesis of first normal form. That's like exactly what first normal form is about.
  - goto11 5y ago
    1NF as defined by E.F.Codd literally does not allow relation-values attributes.
    - blacktriangle 5y ago
      Which is why it's worth even specifying 1NF. That doesn't mean that the relational algebra does not allow for them. I think the reason people even ask questions like what does 1NF even mean is because they're not learning relational algebra, they are learning SQL. So if every table is in 1NF by default, it begs the question what is the point of 1NF? And the point is that the relational algebra is bigger than SQL, a realization that leads you to potentially interesting projects like CJ Date's efforts to build truly relational databases.
      - goto11 5y ago
        But isn't Codd's relational algebra based on 1NF relations? The point of 1NF is (among other things) to make the algebra simpler because you don't need to handle nested relations. A database built on non-1NF relations would be very different from Codd's vision.
      - CRConrad 5y ago
        > So if every table is in 1NF by default, it begs the question what is the point of 1NF? Your premise isn't fulfilled: Not every SQL table is in 1NF by default, it's quite easy to fuck that up. As illustrated by the whole "list of telephone numbers in a single column" point discussed here.
        goto11 5y ago
        This is what the article discuss. 1NF is about eliminating nested tables, which is not supported by SQL. Dumping a list of phone numbers into a string does not violate 1NF because a string is still just a single value from the perspective of the database.
        CRConrad 5y ago
- deleted 5y ago
  [deleted]
- brazzy 5y ago
  You're not really addressing the point of the comment you are replying to, which is about what constitutes a normalized relation, not what constitutes a relation. And I agree, TFA is just flat out wrong. A relation Manager with a CSV-valued column SubordinateIds or columns named "Subordinate1Id" through "Subordinate9Id" is most definitely not normalized because those are in effect a (bad) simulation of hierarchical-database-style repeating groups. That they have distinct names (or positions in the CSV list) is an artifact of the representation and not really part of the data model.
  - mamcx 5y ago
    To be pedantic, is not a contradiction. Is a sign of problem with the design? Probably! Most likely! But the relational model CAN'T say it. The normal forms are properties of the way you can do the design, but can't go that Deep. In other words: You can adhere 100% on SOLID OOP principles and your code is still conceptually wrong! If pay attention to the article, his issue is that people latches on the apparent definition of 1st normal form, but misunderstanding it and probably coming to the wrong conclusion about how make the tables...
- gfody 5y ago
  it's all a subtle misunderstanding of who's doing the considering and what's being considered. the author wants to say 1nf is of no concern to users of rdbms, that they can't actually violate 1nf because the system doesn't violate 1nf - however when users are considering normal form it's about the information (the phone numbers) not the data (a string) and in that sense the information is not normalized due to violating first normal form. a different audience, say the rdbms designers, can't have concerns about anything beyond the string - they don't care what you stash in there it's just data and in that sense the data is normalized but the information is not. as an aside I think a lot of this sprouts from subtle ambiguity in the language. you really shouldn't just drop all articles and expect your english to still make sense!
dragonwriter 5y ago
> I think he contradicts himself and confuses the data with the information. I would say he is confusing the physical representation with the logical model.
- goto11 5y ago
  Can you elaborate on why you think that? 1NF does not say anything about physical representation, it only talks about the logical model.
goto11 5y ago
> I don't think delimited values representing a table in a column can be considered normalized. The question is what "normalized" means. It does not mean "best practice data modelling" which is a much broader subject. The article does not say delimited values are good design.

by gfody 5y ago

mamcx 5y ago

No, is not a contradiction. Is like say "Is wrong to store a large json in a cell in an array, that make it not an array". A relation is A VALUE. That is. The atomicity of the data INSIDE each "cell" is orthogonal to the fact the that relation is a relation. This is the point.

blacktriangle 5y ago
For even more fun mind bending, there's absolutely nothing that violates the relational model in having relation-valued attributes. SQL doesn't support that, but that doesn't mean its non-relational.
- derefr 5y ago
  Re: DB support for relations as values — while no DB I know of supports full relations (sets of tuples) in values, composite-typed (single-tuple) columns are a thing, e.g. the on_hand example table in https://www.postgresql.org/docs/current/rowtypes.html https://www.postgresql.org/docs/current/rowtypes.html Also, presuming DB support for some format like JSON, you can encode a relation into a JSON value, stick it into a column and then, at query time, use a function like Postgres's json_to_recordset(json) to emit a relation (set of tuples). Postgres doesn't know how to deal with it "as is" (i.e. it forces you to treat the result of a function as a named joined table in your query, rather than treating it as an concrete value) but it does understand where it is in relation (heh) to the rest of the row, since you can unnest() values from this virtual table and get the right results out. As awful as that sounds, we're actually doing that in production to normalize data coming from redis_fdw (which gives you, basically, (key text, value jsonb) rows for Redis hashes) into relational shape.
- fuckf4ce 5y ago
  Relation valued attributes are precisely the antithesis of first normal form. That's like exactly what first normal form is about.
- goto11 5y ago
  1NF as defined by E.F.Codd literally does not allow relation-values attributes.
  - blacktriangle 5y ago
    Which is why it's worth even specifying 1NF. That doesn't mean that the relational algebra does not allow for them. I think the reason people even ask questions like what does 1NF even mean is because they're not learning relational algebra, they are learning SQL. So if every table is in 1NF by default, it begs the question what is the point of 1NF? And the point is that the relational algebra is bigger than SQL, a realization that leads you to potentially interesting projects like CJ Date's efforts to build truly relational databases.
    - goto11 5y ago
      But isn't Codd's relational algebra based on 1NF relations? The point of 1NF is (among other things) to make the algebra simpler because you don't need to handle nested relations. A database built on non-1NF relations would be very different from Codd's vision.
    - CRConrad 5y ago
      > So if every table is in 1NF by default, it begs the question what is the point of 1NF? Your premise isn't fulfilled: Not every SQL table is in 1NF by default, it's quite easy to fuck that up. As illustrated by the whole "list of telephone numbers in a single column" point discussed here.
      - goto11 5y ago
        This is what the article discuss. 1NF is about eliminating nested tables, which is not supported by SQL. Dumping a list of phone numbers into a string does not violate 1NF because a string is still just a single value from the perspective of the database.
        CRConrad 5y ago
deleted 5y ago
[deleted]
brazzy 5y ago
You're not really addressing the point of the comment you are replying to, which is about what constitutes a normalized relation, not what constitutes a relation. And I agree, TFA is just flat out wrong. A relation Manager with a CSV-valued column SubordinateIds or columns named "Subordinate1Id" through "Subordinate9Id" is most definitely not normalized because those are in effect a (bad) simulation of hierarchical-database-style repeating groups. That they have distinct names (or positions in the CSV list) is an artifact of the representation and not really part of the data model.
- mamcx 5y ago
  To be pedantic, is not a contradiction. Is a sign of problem with the design? Probably! Most likely! But the relational model CAN'T say it. The normal forms are properties of the way you can do the design, but can't go that Deep. In other words: You can adhere 100% on SOLID OOP principles and your code is still conceptually wrong! If pay attention to the article, his issue is that people latches on the apparent definition of 1st normal form, but misunderstanding it and probably coming to the wrong conclusion about how make the tables...
gfody 5y ago
it's all a subtle misunderstanding of who's doing the considering and what's being considered. the author wants to say 1nf is of no concern to users of rdbms, that they can't actually violate 1nf because the system doesn't violate 1nf - however when users are considering normal form it's about the information (the phone numbers) not the data (a string) and in that sense the information is not normalized due to violating first normal form. a different audience, say the rdbms designers, can't have concerns about anything beyond the string - they don't care what you stash in there it's just data and in that sense the data is normalized but the information is not. as an aside I think a lot of this sprouts from subtle ambiguity in the language. you really shouldn't just drop all articles and expect your english to still make sense!

dragonwriter 5y ago

> I think he contradicts himself and confuses the data with the information. I would say he is confusing the physical representation with the logical model.

goto11 5y ago
Can you elaborate on why you think that? 1NF does not say anything about physical representation, it only talks about the logical model.

goto11 5y ago

> I don't think delimited values representing a table in a column can be considered normalized. The question is what "normalized" means. It does not mean "best practice data modelling" which is a much broader subject. The article does not say delimited values are good design.