3 ms·

> Schemaless does not exist. There is always a schema... I also think so but the issue has several important aspects which justify the use of the separate term

> Schemaless does not exist. There is always a schema... I also think so but the issue has several important aspects which justify the use of the separate term "schemaless". One of them is that schema elements (say, column names) can be stored as normal data. For example, instead of having normal columns like Name, Age, Department, we could introduce a column storing these strings in 3 rows. As a result, DBMS is simply unaware of the schema - the schema exists only in our head (and in the app). As a consequence, DBMS cannot help us too much in managing data, and instead our app becomes responsible for these tasks, hence we get problems you mentioned. But the major problem is that currently there is no technology that allows us to say that this table column stores actually column names which can be used in queries and have to be treated as normal columns.

MarHoff 9y ago
Use jsonb inside a Database and use constraints to ensure all requested json attributes (store name,etc..) are present at INSERT/UPDATE time? That don't seems like rocket science to me... duh... PS: But of course don't store ID in an object value or you will lost major benefit of RDMS relational constraints.
- jrochkind1 9y ago
  Well, now you're just as 'schemaless' as any NoSQL, so that's neither an advantage nor a disadvantage over it. There are other differences, both advantages and disadvantages to doing this with Postgres (which is the only db with a type called 'jsonb' I think?), vs some kind of non-SQL document store. I agree that as Postgres json(b) gets better and better, the disadvantages decrease. And one of the main advantages to me is simply not having to have another system to run and understand, since postgres is probably already there. But it's always trade-offs and choices, that you make better with more experience and better understanding of your domain. It's definitely not a "duh, it's always obvious" thing. Unless your domain is so simple that it doesn't hardly matter.
- SmellTheGlove 9y ago
  > PS: But of course don't store ID in an object value or you will lost major benefit of RDMS relational constraints. Or anything that you might want to use as a foreign key down the road. It requires you to know that now, or modify later, so we might as well declare a schema and manage it unless we're totally certain we won't need any additional keys. But if we're certain of that, we could just use a schema.
  - MarHoff 9y ago
    But do schema-less implement foreign constraints at all???? I'm currently implementing a mixed solution where clearly defined properties (and thus ID) have columns and constraints but I also include a schema-less "all you can eat" jsonb field so that experimental properties can be stored. Upon each release I will migrate validated shema-less properties to static columns or even sub-tables. Similarly if a column is remove on next iteration I might transfer attributes to the jsonb to revert it later if needed. This does include a migration step but this seems only like good practice. Of course scaling... but I'm really not limited by this now. The client will interact with the model through views or functions so that he don't have to care if columns are real or not. But anyway as soon as PK/FK are needed I will probably straight-out think that jsonb will be insufficient and take 5 minutes to think of a relational way to implement.
    - SmellTheGlove 9y ago
      I don't see a problem with what you're doing as a means of development, since you're basically stuffing a bunch of data that you may or may not need in the future into a single field with the intent of breaking it out later if you do. I will ask whether this is really saving you any time or simplifying your development at all, given that you essentially impose structure iteratively as the need arises. You're not really schema-less, you're schema-lite, so you're maintaining some structure somewhere. Also, depending on the contents of the jsonb field, you may be defining its structure somewhere, even if it's just naming the elements. I don't have a lot of experience with it, but Postgres supports indexing on jsonb fields. If you do end up in a situation where your keys are in the right place and you have some data in a jsonb, you may still be able to get at it efficiently. Part of this is how we approach development as well. For one, I'm now some management type that makes powerpoints, so any code I write is on my own projects. But when I did more of this, my approach was always to start with an RDBMS unless I had a pretty specific use case to not use one. Having done it long enough, I am being honest that I find maintaining a database schema, migrating changes, and all of that to be pretty trivial. That's my approach, but I don't see an issue with yours if you think it's making you more efficient. I should ask, because I'm assuming it, but make sure whatever you're stuffing into the jsonb field is true row-level data. Try to avoid putting data in there that "belongs" on another row - at the extreme it could provide some unintended exposure. Hypothetically, a row containing Customer A's demographics in the "person" table has a jsonb field - it may be tempting to put some basic demos about other customers B, C and D connected to that customer A in some way, but that's data you'd not want to expose as belonging to Customer A. Yet, if it comes back on the same row, it's as trivial to leak it as it is to retrieve it. EDIT: To actually answer your question, I don't see how schema-less can have foreign constraints. Foreign constraints are defined in the schema. What I was saying is that if you're going to need joins later, have enough schema to support that.
SmellTheGlove 9y ago
> currently there is no technology that allow us to say that this table column stores actually column names that can be used in queries and have to be treated as normal columns. That's because the column name is part of the RDBMS' structure, as is the type, for the most part. If you want to read a column and use the row values as the column names, that can be done, it's just a query. The DBMS is going to have to scan the whole table (or lean on the index) for that column anyway. I can't think of why I'd want the RDBMS to do that for me and imply the column names, rather than me implement it in code. I'm managing the column names as data at that point. That's if you still think it's a good idea. The other piece that jumps out at me is that when you do something like that, you're really building a variably wide table, but not letting the DBMS manage it as a wide table. Instead, every query is going to require either you or the DBMS to transpose rows to columns at some point in the query - if it's a simple select, only on the return, which won't be that expensive, but some joins are going to require it to happen earlier. I'm not smart enough to know whether it'll be computationally expensive to the point of being a showstopper, but I do know it'll require a lot of memory if you want it done fast, or you have to do it slow in scratch/spool.
dragonwriter 9y ago
> I also think so but the issue has several important aspects which justify the use of the separate term "schemaless". One of them is that schema elements (say, column names) can be stored as normal data. It is fundamental to the relational model that metadata like that is stored in and queryable from relations just like normal data.

MarHoff 9y ago

Use jsonb inside a Database and use constraints to ensure all requested json attributes (store name,etc..) are present at INSERT/UPDATE time? That don't seems like rocket science to me... duh... PS: But of course don't store ID in an object value or you will lost major benefit of RDMS relational constraints.

jrochkind1 9y ago
Well, now you're just as 'schemaless' as any NoSQL, so that's neither an advantage nor a disadvantage over it. There are other differences, both advantages and disadvantages to doing this with Postgres (which is the only db with a type called 'jsonb' I think?), vs some kind of non-SQL document store. I agree that as Postgres json(b) gets better and better, the disadvantages decrease. And one of the main advantages to me is simply not having to have another system to run and understand, since postgres is probably already there. But it's always trade-offs and choices, that you make better with more experience and better understanding of your domain. It's definitely not a "duh, it's always obvious" thing. Unless your domain is so simple that it doesn't hardly matter.
SmellTheGlove 9y ago
> PS: But of course don't store ID in an object value or you will lost major benefit of RDMS relational constraints. Or anything that you might want to use as a foreign key down the road. It requires you to know that now, or modify later, so we might as well declare a schema and manage it unless we're totally certain we won't need any additional keys. But if we're certain of that, we could just use a schema.
- MarHoff 9y ago
  But do schema-less implement foreign constraints at all???? I'm currently implementing a mixed solution where clearly defined properties (and thus ID) have columns and constraints but I also include a schema-less "all you can eat" jsonb field so that experimental properties can be stored. Upon each release I will migrate validated shema-less properties to static columns or even sub-tables. Similarly if a column is remove on next iteration I might transfer attributes to the jsonb to revert it later if needed. This does include a migration step but this seems only like good practice. Of course scaling... but I'm really not limited by this now. The client will interact with the model through views or functions so that he don't have to care if columns are real or not. But anyway as soon as PK/FK are needed I will probably straight-out think that jsonb will be insufficient and take 5 minutes to think of a relational way to implement.
  - SmellTheGlove 9y ago
    I don't see a problem with what you're doing as a means of development, since you're basically stuffing a bunch of data that you may or may not need in the future into a single field with the intent of breaking it out later if you do. I will ask whether this is really saving you any time or simplifying your development at all, given that you essentially impose structure iteratively as the need arises. You're not really schema-less, you're schema-lite, so you're maintaining some structure somewhere. Also, depending on the contents of the jsonb field, you may be defining its structure somewhere, even if it's just naming the elements. I don't have a lot of experience with it, but Postgres supports indexing on jsonb fields. If you do end up in a situation where your keys are in the right place and you have some data in a jsonb, you may still be able to get at it efficiently. Part of this is how we approach development as well. For one, I'm now some management type that makes powerpoints, so any code I write is on my own projects. But when I did more of this, my approach was always to start with an RDBMS unless I had a pretty specific use case to not use one. Having done it long enough, I am being honest that I find maintaining a database schema, migrating changes, and all of that to be pretty trivial. That's my approach, but I don't see an issue with yours if you think it's making you more efficient. I should ask, because I'm assuming it, but make sure whatever you're stuffing into the jsonb field is true row-level data. Try to avoid putting data in there that "belongs" on another row - at the extreme it could provide some unintended exposure. Hypothetically, a row containing Customer A's demographics in the "person" table has a jsonb field - it may be tempting to put some basic demos about other customers B, C and D connected to that customer A in some way, but that's data you'd not want to expose as belonging to Customer A. Yet, if it comes back on the same row, it's as trivial to leak it as it is to retrieve it. EDIT: To actually answer your question, I don't see how schema-less can have foreign constraints. Foreign constraints are defined in the schema. What I was saying is that if you're going to need joins later, have enough schema to support that.

SmellTheGlove 9y ago

> currently there is no technology that allow us to say that this table column stores actually column names that can be used in queries and have to be treated as normal columns. That's because the column name is part of the RDBMS' structure, as is the type, for the most part. If you want to read a column and use the row values as the column names, that can be done, it's just a query. The DBMS is going to have to scan the whole table (or lean on the index) for that column anyway. I can't think of why I'd want the RDBMS to do that for me and imply the column names, rather than me implement it in code. I'm managing the column names as data at that point. That's if you still think it's a good idea. The other piece that jumps out at me is that when you do something like that, you're really building a variably wide table, but not letting the DBMS manage it as a wide table. Instead, every query is going to require either you or the DBMS to transpose rows to columns at some point in the query - if it's a simple select, only on the return, which won't be that expensive, but some joins are going to require it to happen earlier. I'm not smart enough to know whether it'll be computationally expensive to the point of being a showstopper, but I do know it'll require a lot of memory if you want it done fast, or you have to do it slow in scratch/spool.

dragonwriter 9y ago

> I also think so but the issue has several important aspects which justify the use of the separate term "schemaless". One of them is that schema elements (say, column names) can be stored as normal data. It is fundamental to the relational model that metadata like that is stored in and queryable from relations just like normal data.