4 ms·

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, bu

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
        The article is wrong: There is no "from the perspective of the database" here. Normalisation is a logical-level process, not physical. What we're using the database to do is to store information about the real world. 1NF is about eliminating nested relations. A list of telephone numbers is a relation -- a one-row table, if you will. > Normalized relations, and the relationship between one normalized relation and another, mirror real-world concepts and their interrelationships. -- https://en.wikipedia.org/wiki/Database_normalization#Minimize_redesign_when_extending_the_database_structure https://en.wikipedia.org/wiki/Database_normalization#Minimiz... In the real world, a phone call is to a single phone number, not to a list of them. > To satisfy First normal form, each column of a table must have a single value. Columns which contain sets of values or nested records are not allowed. -- https://en.wikipedia.org/wiki/Database_normalization#Satisfying_1NF https://en.wikipedia.org/wiki/Database_normalization#Satisfy... A list of telephone numbers is a set of values, not a single value. Several strings "in one".

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
      The article is wrong: There is no "from the perspective of the database" here. Normalisation is a logical-level process, not physical. What we're using the database to do is to store information about the real world. 1NF is about eliminating nested relations. A list of telephone numbers is a relation -- a one-row table, if you will. > Normalized relations, and the relationship between one normalized relation and another, mirror real-world concepts and their interrelationships. -- https://en.wikipedia.org/wiki/Database_normalization#Minimize_redesign_when_extending_the_database_structure https://en.wikipedia.org/wiki/Database_normalization#Minimiz... In the real world, a phone call is to a single phone number, not to a list of them. > To satisfy First normal form, each column of a table must have a single value. Columns which contain sets of values or nested records are not allowed. -- https://en.wikipedia.org/wiki/Database_normalization#Satisfying_1NF https://en.wikipedia.org/wiki/Database_normalization#Satisfy... A list of telephone numbers is a set of values, not a single value. Several strings "in one".