SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Where's the Schema?


Where's the Schema?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)SSC Guru (620K reputation)

Group: Administrators
Points: 620886 Visits: 21265
Comments posted to this topic are about the item Where's the Schema?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Dalkeith
Dalkeith
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2999 Visits: 1407

As someone who has never really worked in a professional development team , I'm more of an all rounder who occasionally writes front ends for internal enterprise use only. I am like yourself Steve slightly skeptical. To me No SQL is an interesting idea that looks like it has some merits in some circumstances . I haven't tried any of the databases themselves but try to understand the concepts.

I have thought that I might be able to store all data for any conceivable application in two tables - a key value table and a schema table. Such that these keys can be associated with multiple schemas. This to my mind would allow developers to create columns on the fly while not worrying about the schema and then I can retrospectively go back and assign such fields to some kind of schema. I can see that this would have merit in certain circumstances for things like CRM notes applications. I just can't see that it would be good for design of financial systems hr systems / pretty much anything involving money. Master child forms for desktop applications would appear more work if you use this kind of no sql format - interestingly I think standard web frameworks are very very poor with master child forms anyway and I think this is one of the driving forces to why no sql is so appealing to web developers as it makes it very easy to records standard master child information in a flat form while removing additional development time consulting with the DBA. A nice half way house might be implementation of a key value table with the requirement that developers restrict the ability of users to create "columns" in some way and impress upon them the need to make every effort to remove (in some way) duplicate columns that confuse users and lead to problems with schema definition.

Without some kind of structure users subsequently analyzing information will definitely miss more stuff than with standard rdbms as individuals miss columns or struggle to distil data down.

Open Street Map uses this key value no sql structure albeit managed through an RDBMS (postgres) where users can create columns. The single table is massive in terms of columns and interestingly it does seem to have come to some kind of stable limit , analyzing data becomes an exercise in fuzzy matching and thus two people can get a different answer to the same question. This was the right decision for them though I think because they have tens of thousands of users and very few developers the amount of data being collected is huge and the system copes well and has flexibility.


call.copse
call.copse
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14761 Visits: 2465
@Dalkeith I would associate NoSQL more with document / JSON stored values rather than EAV. EAV would certainly never scale whilst a JSON type approach can scale, with a schema based on on ignoring things you don't need to know about. It's a decent plans for some use cases anyhow...not ones I use, but that I can envisage. I think Steve is right about schemas though.
Dalkeith
Dalkeith
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2999 Visits: 1407
Thanks CC - noted.
peter.row
peter.row
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3654 Visits: 584
I'm a dev who has moved more to lead dev role, and for the record I've predominantly used SQL Server for all data storage.

For the types of projects I work on anything with out a defined schema = NO! Customers want to be able to create their own reports and as soon as you need to do that then storing arbitrary things like JSON that could represent anything immediately makes this a nightmare. And maintaining one store for JSON etc.. and one for some deserialized versus in a relational database seems like something that is doomed to cause nothing but headaches for all involved.
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)SSC Guru (111K reputation)

Group: General Forum Members
Points: 111882 Visits: 14974
.. There's always a schema, and the rules have to be implemented up front, or later on. Whether you use a RDBMS or a NoSQL store, you are going to be dealing with a schema. The question is do you want to deal with it in a central location or in every application? ..

The reason why most application developers choose a document database is that they simply don't want to "deal with" the schema at all. A java or C# object can be transparently persisted to the database as a JSON object. What happens on the front end is a single method call submitting the ID or session token, and what happens on the backend is a single bookmark lookup, returning a document containing all relevant related data for that customer. For example, a document database is ideal of an eCommerce shopping cart or a mobile app with microservice based architecture. It's schemaless in the sense that they are not designing the schema for a set of relational database tables, they are simply designing the properties of an object class. Maintaining multiple versions of a schema within a collection is technically possible, but that's not the goal.

I'm currently working on a project where a customer base of several million can access their account information and purchase / payment history from a mobile app. We're considering Azure DocumentDB. As a DBA responsible for the availability and performance of SQL Server, I appreciate the fact that there is another option besides having the production online database (or even the data warehouse) getting pounded by thousands of requests per second. Yes, SQL Server can scale up to the task, it's been done before, but the level of effort and expense would be the equivalent of deploying an army to the moon.



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
IowaDave
IowaDave
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1388 Visits: 653
As time goes on, I'm beginning to see some value in the whole "schema-less" concept. One use-case is for Proofs of Concept. Being able to rapidly create something and just mash whatever data you're collecting in a single blob in the database. That makes for quicker development (no need to map individual values to database columns) and yet it still gives you access to the data. Later on, the application could be changed to store it in columns, or perhaps it doesn't. Another app could be written to take that "blob" (JSON, etc.) and map it to another table with user-friendly columns, etc. for such things as reporting.

The old adage of "if I have a hammer, everything looks like a nail" seems to hold true here. If I know (and love) SQL Server and its relational-concepts, I tend to want to force everything into that paradigm, but it's good to understand and explore other options. ... Not everything is a nail! Wink
peter.row
peter.row
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3654 Visits: 584
IowaDave - Tuesday, July 3, 2018 8:26 AM
As time goes on, I'm beginning to see some value in the whole "schema-less" concept. One use-case is for Proofs of Concept. Being able to rapidly create something and just mash whatever data you're collecting in a single blob in the database. That makes for quicker development (no need to map individual values to database columns) and yet it still gives you access to the data. Later on, the application could be changed to store it in columns, or perhaps it doesn't. Another app could be written to take that "blob" (JSON, etc.) and map it to another table with user-friendly columns, etc. for such things as reporting.

The old adage of "if I have a hammer, everything looks like a nail" seems to hold true here. If I know (and love) SQL Server and its relational-concepts, I tend to want to force everything into that paradigm, but it's good to understand and explore other options. ... Not everything is a nail! Wink


Except that there's that other problem where the prototype becomes the real thing and you get lumbered with a load of technical debt.
Using a relational database is not really a hammer. Your comments main assertion is to save time not because things don't fit into relational concepts.

My problem with conference vids I've seen on this topic is that as soon as you need reporting they completely fall apart and become more of a burden then just doing relational SQL in the first place.
richard.gardner87
richard.gardner87
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 12
I've hit some nasty messes when people try and store master data in No SQL, the devs have literally lost the ability to rebuild the data hierarchy. After conversations with fellow professionals it seems I'm not alone, and my conclusion is that it's only good for really basic list management (Craig's list is a good example).

It's a trade off between consistency and hierarchy management vs speed.

I've spoken to devs working on web performance issues who go running off to build NoSQL solutions to solve web response problems, when really their actual problem is terrible terrible SQL architecture. Result? The architecture is still terrible plus they have a sprawling, unmanageable No SQL mess to manage too.

Web devs tend to solve the problem in front of them with whatever tools they fancy having a play with. They shouldn't be given their heads on this.

I am sure eventually web development will be mature enough to build an all encompassing data solution, but not today, they're still generating the same problems I've been solving for 20 years. SQL is king, it's maybe not very trendy or exciting but it's the best tool for managing modelling state transitions in business processes by quite a margin.
roger.plowman
roger.plowman
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8923 Visits: 2019
I think the real appeal of "schema-less" (actually, it should probably be called "ad-hoc schema" since nothing is schema-less) is that new "table structures" can be defined without regard to existing ones. My hobbies usually involve the need for LOTS of data storage and I once tried to create a database for D&D world creation. This meant I needed *thousands* of tables, one per different kind of object (planets, countries, skills, spells, creatures, you name it...).

I used Access (which is a relational DBMS regardless of what detractors would have you believe) and the result was a set of half a dozen tables to handle "ad hoc" tables. These tables contained the details of the "tables". The user could create new tables (actually, object blueprints) to their hearts' content, and export them to other users (both "schema" and data). It worked very, very well for what it was intended for.

In short it was almost identical to the way SQL Server works! Tongue

SQL Server does EXACTLY the same thing, in a far more sophisticated way. Every time you create a table it goes in a system table. Ditto for columns, indexes, permissions, etc.

So in effect a no-SQL database does exactly what a SQL database does, just less efficiently and in a less granular fashion. Developers think this is easier, and then spend all their time replicating features SQL Server provides for almost free.

It's really no different than building every table with NVARCHAR(MAX) columns for everything, in other words they're a bit ignorant of helpful features.

I suspect there ARE use cases where no-SQL is a better fit. Maybe important ones (Google indexing comes to mind, or YouTube).

But IMO the vast majority of cases are better off with structured schema, be it SQL or something similar.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search