Oh wow. I'd never looked close up. That's fascinating, and a term of art I expect to use in the future.

Btw I think your SQL example is called "EAV Schema" and does have a legitimate purpose once in a while.

I think it was coined here https://thedailywtf.com/articles/The_Inner-Platform_Effect (Haven't thought about that website in a while)

I've always wondered why more people didn't actually issue DDL for these kinds of databases. Like if you want to let your user define their own custom fields, just take what they enter and actually CREATE TABLE / ALTER TABLE to build the the tables they need. And then more or less you could just write regular queries.

I've worked on a system like that, writing software on a project base for enterprise clients.

Turns out sysadmins get REALLY nervous when you tell them your application needs permission to use DDL at runtime. It required a lot more filling out forms.

"Just" don't use the same database that the application is using for it's internal data.

You can scope the DDL permissions to a single schema while still using the same database (and benefit from all the transactional guarantees a single database offers).

Yes, if database supports it (I guess every major ones now do) then it's great. Postgres's transactional DDL seems like great fit.

I'm sorry, I don't follow.

I was talking about an application and its accompanying database, which would still be paid for and administered by the customer, not a database outside the application which it would be accessing.

That still raised many eyebrows.

I think the GP was assuming the reason sysadmins worry about running DDL is the danger of overwriting/modifying tables the application depends on.

If this is the main concern, you could have two databases (or schemas, if your database supports it).

One for application configuration/storage that the application never runs DDL on and another for user data, where the tables can be created/modified dynamically.

That separation means that, at worst, user data is screwed up if there's a bug in the DDL generation, but the application should still be able to run.

At least, that was my read.

This is how arcgis works and it really sucks when your tier 1 starts adding columns to your schema because they made a typo.

Some people do that, and at one point it was much faster to do that so it was more common, but as databases have generally gotten faster that kind of optimization is less interesting to people.

I assume performance is a lot less predictable with that method, no?

these days if you are modeling the "sparse, high-dimensional data" of the kind that EAV schema does well, you probably should be reaching for JSON columns instead of EAV schema.

Some databases support "sparse columns", which use EAV under the hood but are better optimised: https://docs.microsoft.com/en-us/sql/relational-databases/ta...

Thanks, I forgot about that! :)

Isn't that the problem that these "columnar store" DBs address?

A bunch of dictionaries containing UUID/data, where each dimension is its own dict, and the dicts might or might not be running on the same system?

> "EAV Schema" and does have a legitimate purpose once in a while

Well, technically, the database's own system tables have tables with such column names and responsibilities.

There's a reason for that. The attribute statement / attribute name / attribute value style has a consistent schema that won't change despite any custom attributes that are being added. Your "claims" example does not have a consistent schema; it changes when new claims are added, and therefore makes it impossible to validate the response (the notion of arbitrary objects does not exist in XML schemas).

Typically this would be implemented by having a set of predefined elements in the XSD (name, email, etc...) and also an 'additional' element that has a list of "Any" as the children. Easy!

E.g.:

    <claims>
        <nameid>[email protected]</nameid>
        <givenname>Foo</givenname>
        <surname>Bar</surname>    
        <extensions>
            <myCustomClaim>010231239dfadsf</myCustomClaim>
        </extensions>
    </claims>

It's also possible to use namespaces for extensibility, and this can even be used to guarantee that the tag names don't conflict.

So instead of this garbage:

    <saml:AuthnContextClassRef>urn:oasis:names:tc:SAML:2.0:ac:classes:Password</saml:AuthnContextClassRef>

You'd have standard namespaced and extensible elements such as:

    <claims xmlns:saml="urn:oasis:names:tc:SAML:2.0:ac:classes" xmlns:adfs="urn:com:microsoft:ADFS">
        <saml:nameid>[email protected]</saml:nameid>
        <saml:givenname>Foo</saml:givenname>
        <saml:surname>Bar</saml:surname>    
        <adfs:securityId>S-0-1-52534-12362341234-12312315</adfs:securityId>
        <adfs:objectGuid>47e3a2d3-266a-48ad-b080-b785b4d0b45f</adfs:objectGuid>
    </claims>

This would enable clients to validate the claims they do understand and ignore the claims they don't. Similarly to how X.509 does it, elements could be added to "must process" or "optional" parent elements to ensure correctness and security even in the face of a wide range of client capabilities.

This is common in the XML world, but SAML had to invent its own wheels.

Surely there are better ways to do that, if it is even needed. If your validation only validates that you have

> attribute statement / attribute name / attribute value style

Does that even have a benefit? Is it any different that simply validating that a string is valid xml/json/something syntax?

> there's one table with the columns "rowid, columnname, value"

Yeah, that's known as "entity-attribute-value model" (or just "EAV") and is considered an anti-pattern unless you actually need the flexibility it provides (which is fairly rare).

Yup. We run a system that uses this pattern for user-defined data fields. It works, but not without difficulty. It's very difficult to validate with business rules because you can't eliminate nulls and the "value" field is forced to be a character field. The system does have type checking enforced by the application, but multiple applications access the data.

> the "value" field is forced to be a character field

It doesn't have to be. We use this (under SQL Server) for our simple "custom attributes" mechanism (some irrelevant details omitted):

    CREATE TABLE private_data.ATTRIBUTE_VERSION (

        OBJECT_ID bigint,
        ATTRIBUTE_NAME nvarchar(255),
        ATTRIBUTE_GEN bigint,
        IS_TRASH bit NOT NULL,

        VALUE_BOOL bit,
        VALUE_INT bigint,
        VALUE_DECIMAL decimal(38, 9),
        VALUE_DATETIME datetime2,
        VALUE_STRING nvarchar(255),

        ATTRIBUTE_TYPE AS private_attributes.ATTRIBUTE_VALUE_TO_TYPE(
            VALUE_BOOL,
            VALUE_INT,
            VALUE_DECIMAL,
            VALUE_DATETIME,
            VALUE_STRING
        ),

        CONSTRAINT ATTRIBUTE_VERSION_PK PRIMARY KEY (OBJECT_ID, ATTRIBUTE_NAME, ATTRIBUTE_GEN),

        CONSTRAINT ATTRIBUTE_VERSION_FK1 FOREIGN KEY (OBJECT_ID, ATTRIBUTE_NAME) REFERENCES private_data.ATTRIBUTE,
        CONSTRAINT ATTRIBUTE_VERSION_FK2 FOREIGN KEY (ATTRIBUTE_GEN) REFERENCES private_data.GENERATION,

        CONSTRAINT ATTRIBUTE_VERSION_C1 CHECK ( -- At most one of the various value types can be non-NULL.
            private_attributes.ATTRIBUTE_VALUE_CHECK(
                VALUE_BOOL,
                VALUE_INT,
                VALUE_DECIMAL,
                VALUE_DATETIME,
                VALUE_STRING
            ) = 1
        )

    );

Since SQL Server encodes NULLs in a bitmap, having a bunch of NULLs for every non-NULL takes almost no extra space at all. But even on a "wasteful" database such as Oracle, this shouldn't add more than an extra byte per NULL.

> The system does have type checking enforced by the application, but multiple applications access the data.

Traditionally, this is solved by funneling all applications through a public API of stored procedures and views/functions which enforce the desired business logic, but I understand this approach has fallen out of favor in recent years (decades?).

That's really not an improvement. This complex enough that you're going to run into problems when multiple systems use the same database. I can imagine the fun that comes when another system wedges a field because it used the wrong type. That's the same problem we already have with a single text column. This is way more complex and still has the same problems.

The reason for that is that you still want to be able have an XSD to validate.

In the context of SAML, these values are completely arbitrary metadata without fixed meaning in the protocol itslef.

You could even use attribute names that aren't valid for XML tag names.

It's the same reason why -- for example -- AWS APIs return tags as a similar entity list. You don't see

    <Name>Example</Name>
    <BillingGroup>prod</BillingGroup>

but rather

    <tagSet>
      <item>
        <key>Name</key>
        <value>Example</value>
      </item>
      <item>
        <key>BillingGroup</key>
        <value>prod</value>
      </item>
    </tagSet>

---

Database EAV would likewise be 100% appropriate if users could use the database to store arbitrary attributes. (Think JIRA custom fields.)

But if users are limited to certain attributes, then encode that in the schema.

>In the context of SAML, these values are completely arbitrary metadata without fixed meaning in the protocol itself.

Well, that's the problem though. YAGNI, KISS, and all that.

>The reason for that is that you still want to be able have an XSD to validate.

Well, you can still validate fine when you have specific fixed metadata...

A big part of SAML development was the attempt to satisfy every single requirement put forth by every stakeholder. The outcome of this was that they couldn't come up with a single schema that covered everything (schema and taxonomies that attempt to cover too much have their issues). They couldn't even agree on what to call a user ID. Instead they ended up with a schema to describe schemas, making SAML able to flex to handle nearly any use case, but resulting in a terrible mess and wrecking any sane interoperability.

What we have now are groups like OASIS, the Shibboleth Consortium, eduGAIN, and InCommon, each defining the schema their members agree to support, and an explosion of competing standards.

By the way, even Microsoft can't interoperate with themselves. Traditional ADFS SAML can handle arbitrary multi-value attributes that Azure ADFS rejects.

The most astonishing thing about the xml ecosystem is that the XPath query language specification standard doesn't use xml syntax.

That's a good thing

Why is it astonishing? The hype wasn't manufactured by the spec authors. The sales departments were responsible for that.

Consider xslt syntax. Consider xsd syntax.

Thank you for this comment. Now, I know, I do not need to learn SAML. I will wait for the next technology. It reminds me how SOAP has been superseded by JSON.