[jawngee]

<sarcasm> Yeah that would be totally awesome! Now our databases can't optimize our query plans and any aggregate functions will be doing super cheap type conversions between strings and number types!

And, really, who cares about data consistency? Sure that stupid little script is inserting strings into what should be a numeric column, but I'm sure that won't cause any problems at all when the monthly reports run, or the batch processes that charge our customers run, because the database will know how to handle such cases because the DB developers have written all that extra logic in it for such cases. </sarcasm>

<reality> Stop being lazy. </reality>

[reg4c]

Data consistency could be maintained through stronger runtime input verification which in turn nulls everything you said.

I don't think that it would be a bad idea to have dynamically typed databases although it would put a little more strain on developing the actual application instead of having to spend time on making the database.

[jawngee]

Yeah, but guess what? More than your single application typically hits your database, so now you have to make sure any and all applications do that runtime input verification and do it correctly.

At Massify, we have 5 different applications with entirely different code bases hitting the same datasources, so now you want me to go and make sure input verification is the same across all of them when I could more easily do it in one source?

It doesn't nullify anything I've said, in fact it proves it!

It would be a HORRIBLE IDEA. Nothing about this is a good idea, if you think it is, please turn in your developer license!

[timwiseman]

Yes, but this is normally a bad trade off. For one thing, if you are in a large organization your DBA (or at least your lead DBA) is often a senior person with a lot of experience and was normally a developer before becoming a DBA. Part of the DBA's job is to protect the database, and data integrity, from developer mistakes. In said large organization most of your developers are younger and less experienced and may not always remember to put in the checks in the application. Code review will certainly help, but even there you often have to validate input in a lot of places where you define database types once.

This relationship changes somewhat in smaller organization with a more hand-picked team of course, but even there it is normally less work to set up the schema properly then to make sure every application that ever touches it properly validates every single piece of data for proper type.

There are times when a dynamically typed database makes sense, for instance when it is primarily an object store for an object oriented program. But if you want a relational database with the data integrity and data analysis capabilities it brings to bear, then it probably makes more sense to use a statically typed database.

[seldo]

And if we all switched to this tomorrow, six months from now somebody would invent amazing new "static type storage", the advantages of which would include "saves you hours of coding those tiresome data-validation routines we all hate so much!"

[elblanco]

This works really well for embedded applications since the code logic should be doing some type checking and verification before insert.

SQLite is a very well known relational database with dynamic typing, it's quite possible the most deployed relational dB in the world since it runs in everything from browsers to mp3 players and if the data inserts are well controlled, don't really result in any particular problems.

[prodigal_erik]

> logic should be doing some type checking and verification before insert

There are a minority of coders who are actually anal enough to get that right 100% of the time in every version of every app that touches the database. But those are not the people who are reckless enough to want to omit types from the schema. If you think doing it the easy and reliable way isn't worth the hassle, you are not actually going to do it the hard way, even if you can convince yourself you will.

[elblanco]

Dunno why you replied to me, but I agree. In single app usage, in a well controlled and well defined situation, it's probably "okay" and you can get away from it. There are some environments like this. But I agree that in the general sense, especially with lots of apps banging on the same set of data, it's bad form.

[prodigal_erik]

I just try to counter the argument "the database doesn't need to validate because the app can do it" wherever I see it, because it just doesn't happen that way even though it's technically possible.

[fadzlan]

It is quite common to have reports, BI tools and other applications going through your database, so now you have to do the checks on different fronts.

And another very important point, applications errors can be hard to fix, but Terabytes of inconsistent data over the years that your business depends on, now that's nightmare.

[noblethrasher]

In fact, I've just come to realize to the benefits of user defined data types, especially for primary keys. You can treat tables as functions and dynamically discover the ways in which you can compose them (i.e. create queries).