Databases Punish You

You can not insert or update something in to a database in at a kind of "sandbox" level it has to be "all correct" or it will not be saved at all.

Exactly, because in many cases HumansDoNotHaveTime and have to immediately experiment with TheCompromise while our attention span is still high. Later when we have more time we can perfect the model to be longer term. At least one can design an unnormalized database though and normalize it with time. This is actually why Excel (and visual MS Access) is such a popular fake database... that people start off with initially. Maybe more visual database tools for products like MySQL, PostgreSql, Oracle need to exist.. to get the ball rolling easier? There are programs such as MySQL Query browser but they aren't as quick as something like Excel. It's hard for every solution to be both scalable in the long term, and quick up front too.

But... is that really the answer? to make it easier to create badly designed databases? in my experience a lot of those systems built in access and in excel end up having such a bad database design that sooner or later you have to completely get rid of them. For example imagine you have to create an inventory system for a government, and one of the requests of the the user to make it possible to define by himself the data model of the items in the inventory (it is not the same data you need for a car, a plane or a x-ray machine). At the beginning it seems like a nice idea, a nice way to avoid having a lot of requests for customization of the system... but think about it later, imagine if 2 users in 2 hospitals create entirely different data models to deal with the same kind of inventory item, while both users have x ray machines in the database, since the data they register is so different, it becomes really hard to generate reports or run analysis processes with data from both users, also imagine the really bad design someone with absolutly no preparation will create (lots of redundancy, catalogs with lots of garbage, tables with data with almost the same meaning, etc, etc). On the other hand if this system is going to be used with thousands of different kinds of inventory items, programmers are in for DeathMarch project.Mmmm....maybe what we need is tools to ManageTheWorkflowOfDatabaseModifications

Absolutely, creating bad databases it is not the answer. I hope he isn't advocating laziness. -- DaveVoorhis

Why do tools such as PhpMyAdmin? and MySQL query browser exist, if people can just use a text editor and console to send the database queries? I am claiming we can improve the query browsers... that's all. They can still map a relational model even though they are more visual.. Even editing the cells in a table (okay, relvars.. forgive me) is possible in MySQL Query Browser.. and phpMyAdmin.
Relvars are not tables. Relation-valued variables (relvars) do not exist in SQL DBMSes because (a) tables are not variables in the usual programming language sense, and (b) tables may permit duplicate rows and are therefore not restricted to containing relations.

If you want to, for example, save a row in the Persons table, and the Persons table has the firstName field as not nullable, you can not insert a person without a firstName, and add the firstName later (before the transaction is committed). Therefore, you loose the ability to use all those things that are so nice about a database until you data is complete and validated, but... what if you need incremental validation? you can not write your incremental validation in SQL, you have to manipulate your data in some kind of external temporal storage. And it is even more annoying when you need to manipulate partial data that belongs to lots of different tables, you can not:

Make the database suspend the referential integrity validations until the transaction is commited.
- Sure you can. See http://www.oracle-base.com/articles/8i/ConstraintCheckingUpdates.php#Deferred, for example.
  - Yes, but only in some databases, and why only for referential integrity? why not also for not nullable fields? or check conditions in fields?
  - SQL implementations are sadly inconsistent in their handling of consistency controls. Welcome to the real world.
And also tell you, if you try to commit, that the data you inserted will violate an referential integrity rule, and allow you to fix it and try to commit again.
- Consider restructuring the client-side of your application. Supporting this should be trivial.
  - Of course, it is trivial, I do it always in the client-side of my applications... my question is... why this is not standardized? why do I have to reinvent this wheel again, and again? why this is not considered and standard minimum service that should be provided by all client side apis for database handling? RelationalWeenies love to say that databases have sound mathematical proofs that make them so great... but they don't stop to think that without equally god client side features, data handling with databases is a real pain.
  - What client-side languages are you using? The few times I've had to do client-side search and sorting in a database-driven application, it's been trivial. As for handling insert errors with re-tries, I "invent" it once per language and then use it. Why have you not standardized it in your own application development process?
    - Oh, I have done it (or I use a product do it, like for example ObjectRelationalMapper, but what bothers me is that must of the Apis for this kind of job are more ObjectOriented than RelationalOriented?, so, in general the kind of persons that understand this need standarization, are ObjectWeenies? not RelationalWeenies, and sometime it is really hard to convince RelationalWeenies to not reinvent the wheel each time this need to be done.

Also I feel this violates OnceAndOnlyOnce because a lot of times you need to re-implement stuff in the client that it is implemented inside the database (if you alredy have code to manipulate data following a particular business rule inside an stored procedure, and you need to do the exact same thing with data that it is not still persisted in to the database, you will have to implement that algorithm twice... how do you solve that? Why all the RelationalWeenie I have personally known do not offer a solution for this?, I think a new kind of client side framework should be created to deal with this problem)

DatabasesPunishYou because they don't let you manipulate data before it matches the database model perfectly, if you data doesn't match, the database completely rejects it and if you didn't save it in an external place, it is lost forever.

That is why, a lot of time, we are forced to build stuff like ObjectRelationalManagers?, and manipulate the data in Java, or CeeSharp, or whatever, because, even if we really want to, we couldn't do the BeforeEverythingIsReadyDataManipulation? in an SqlDatabase?.

See also SoftwareThatPunishesTheUser

This is a feature, not a bug. A database guarantees consistency, such it must be in a consistent state at all times. If the constraints state that firstName is not nullable, then no user, including you, should ever see a null in the firstName field in the database. While this may seen inconvenient from your application's point of view, imagine all the other applications using the same database that would break if their assumption that a firstName can never be null -- as stated by the field constraint -- is suddenly violated.

If you need this kind of capability, you might consider:

Changing the database schema to support the kind of entries you wish to make without violating fundamental constraints. Use a PartialPersons table for scratchwork, where rows are copied to the Persons when complete. This is the preferred solution, because it accurately represents real-world facts.
If you can't alter the schema, use some client-side mechanism, because the DatabaseIsRepresenterOfFacts, not your private scratch space.
Properly employ "deferred constraint checking" settings provided by your DBMS, if appropriate and available.

-- DaveVoorhis

Yes, but:

What if you need to do sorting? the database can not help you because your data is incomplete
What if you need to search? the database can not help you because your data is incomplete
What if you need to group by? the database can not help you because your data is incomplete

So, databases do not work for incomplete information? wouldn't it be nice if I could use the same declarative approach I use for data already persisted in the database with the data that I am still manipulating before I save it in to the database?

See my first bullet point above re using a PartialPersons table, for example. In my twenty-odd years of developing database applications, I've yet to see a circumstance where this didn't solve precisely the problem you've described, along with gaining other benefits in terms of representing real-world facts. I.e., you now have an accurate representation of the fact that there are both complete entries and incomplete entries, which can be queried, summarised, analysed, reported to management, etc. However, if your approach to applications or your domain is fundamentally different from mine such that PartialSomething tables won't work, consider using a local SQL DBMS for scratchwork or a client-side language (or library) that provides richer data manipulation capabilities. The purpose of a database is to represent shared data in a guaranteed-consistent manner, i.e., facts. A database should never, ever, ever, ever, ever represent non-facts. When you write "the database can not help you because your data is incomplete", I interpret "incomplete" to mean "inconsistent." Then I think, "Thank God!" (Or, more accurately, "Thank Codd!") -- DV

Yes, your idea of creating a PartialPersons? tables is a GoodIdea, but it also helps showing the limitations of the relational model (or perhaps just the limitations of current database implementatios?) For example, in this case, PartialPersons? and Persons have exactly the same fields but there is no standard relational way that I know of that allows me to specify that... how do I say, in relational theory, that PartialPersons? and Persons should keep the same structure, but different restrictions, so that it doestn become a relational nighmare to keep them in sync... (and things get even worse if I have lots of interrelated tables, because I end up having a database model, and a mirror database model without the same restrictions, why if it so obvious that this is needed no tools currently exist to deal automatically with this kind of problems at the database level?)

It shows only the limitation of implementations. Perhaps... This issue has nothing to do with the RelationalModel, and I have never found the rare occasions where I need to use such a mechanism to be a problem, in any of the dozens of large multi-user database-driven applications I've developed.

Well, I find that I need to solve this problem pretty often, and I think it is the kind of problem that directly violates OnceAndOnlyOnce.

I can appreciate where you're coming from, and I have encountered a few occasions where the same essential functionality (a conversion routine, for example) needed to be implemented in the database (SQL), the client application language, and in Javascript to support a browser-based front end. I think the solution lies in replacing the current multi-tiered, multi-language, multiple source-bases approach with an approach that uses a single OnceAndOnlyOnce-compliant application language and source base to define and manage the database, define application functionality, and specify the user interface. When functionality needs to be deployed to various distributed nodes, it should be under automated control of an optimiser/deployer, not manual determination of a developer. See the end of the DatabaseType page for more on this. -- DaveVoorhis

It's all a matter of tradeoffs. Do you want to "pay" now or pay later? I do agree that something like DynamicRelational would allow a prototyping angle to be taken at first, but which later also allows one to incrementally "tighten the screws" to add more integrity, validation, TypeSafety, etc. as the project matures and the requirements settle. I agree that often it's not wise to start a project before the requirements are well-known, but in the real world you are often pressured to pull a poorly-defined rabbit out of a poorly defined hat. -t

See DatabaseIsNotYourPrivateScratchSpace, DbasGoneBad, DynamicRelational

InSovietRussia DatabasesPunishYou.

MarchZeroEight

CategoryDatabase