Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    Join Date
    Jun 2009
    Posts
    66

    rant: Why RDMS implementations are not relational

    Foreign Keys: (avoiding surrogates)
    If you use natural keys where the primary key consist of one or more columns, you have to carry them over on all tables that use it as a foreign key. When the primary key consists of 3-4 columns, it's very maintenance heavy. Why doesn't the database automatically keep a "pointer" instead of manually having the DBA define the foreign key columns?

    The DBA should be able to define a primary key on a table A, and then tell the DB that they want table B to relate to table A. The DB then should automatically create a "pointer" between table A and table B and maintain it. This means that if the PK in table A is ever modified (column added etc), the DB would automatically propagate the changes to the reference tables with the "pointer".

    Cascade Updates should be handled by a "pointer" so that when the natural key does change, there is no massive update that occurs etc.

    This is why we keep using surrogates, we don't have this functionality yet. The other argument for surrogates regarding "length and performance" is invalid due to multicore architecture, ssds etc.


    Stored Procedures:
    The relational model deals with sets, RDBMS are suppose to deal with sets, so why is it that stored procedures work "sequentially" row by row? Why can't we pass a set into a stored procedure? (SQL 2008 can with table value constructor but it is so poorly implemented that it is unusable. MS likes to blame the relational model instead of their poor OO-based implementation of it)

    SQL-92 has support for table value constructor but I think the "implementation" (at the moment, only SQL 2008 has it) is wrong.

    SQL 2008 implementation involves in creating a user-defined table type that is "based" off the target table (you must do this manually). This introduces redundancy, if you have a member table...you must create a member table type (maintenance is increased). Why not have an automatically declared type that is based of the target table? I know oracle and postgre has "row types" where you can pass a row (again sequential row by row...not set based). Postgre has an implementation where you can pass a "array" set but it is not valid if you use standard SQL option instead of plpgsql.


    The DB that comes closest to the "relational model" and standard sql is firebird.
    Last edited by sqlguru; 07-07-09 at 10:46.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Once again there is no question. Why are you posting these rants? Get a blog.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by pootle flump
    Once again there is no question. Why are you posting these rants? Get a blog.
    The point is that instead of implementing the correct relational features and standard sql, they are implementing utterly useless crap (xml datatypes etc) to please the OO, RAD, and non-sql people. The "ALTER user defined type" feature was asked back in 2000! How can you not alter something as crucial as a domain? When you use natural keys as PKs where the "length" may change, domains are absolutely required. Again, MS is proposing the surrogate solution. You can tell that they gave table value parameters (constructors) very low priority because you cannot even ALTER it after created WITHOUT dropping ALL references to it. You can't even use them as output parameters because they are read only! The direction for SQL Server is lost, MS is moving away from the relational model (blaming it instead of implementing it correctly).

    I posted them as feedback for SQL Server, maybe you guys can vote.

    https://connect.microsoft.com/SQLSer...dbackID=472957

    https://connect.microsoft.com/SQLSer...dbackID=472960
    Last edited by sqlguru; 07-07-09 at 11:34.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by pootle flump
    Once again there is no question. Why are you posting these rants? Get a blog.
    Well, this is a general discussion area for database concepts. Not wholly inappropriate here, though I have yet to get a response from his earlier posting:
    http://www.dbforums.com/microsoft-sq...ql-server.html
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's a discussion when the questions are not rhetorical.
    These are also not database concepts, but implementation issues.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    SQLguru,

    No DBMS based on the SQL model can ever be relational because the SQL standard is founded on fundamentally non-relational concepts. SQL is a technology of the 1980s whose dominance is now waning. What we are now seeing and can expect to see more of is other database models (the relational model included) that dispense with the need for SQL.

    I hope and believe that the Relational Model will be central to the future of database systems. But that will only happen if we shake off the SQL habit and move on to better things. We can't afford to be sentimental about this in my opinion. SQL cannot be fixed and the only way forward is to replace it with something better.

    You have given two great examples of issues in SQL DBMSs. In the first case, the SQL model makes it unreasonably hard for DBMS vendors to implement different storage optimisation strategies. The redundancy of the SQL language, its lack of orthogonality and the need to support duplicate rows make query rewrites and other physical optimisations much harder than they should be.

    Your second point is yet another SQL legacy. The SQL standard has no concept of a relation variable or relation assignment and probably never will have. If such basic things had been part of the SQL language to start with then database products would naturally support them today. The table value constructor syntax doesn't make up for that glaring omission.

  7. #7
    Join Date
    Jun 2009
    Posts
    66
    When will we see the replacements then? I've read about Dataphor's supposedly "true rdbms" but there's no progress of it.

    The big players are Oracle and MS SQL Server, those are mainstream and SQL isn't going anywhere according to them. We can't move to better things because currently there is no "true rdbms".

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Amazon SimpleDB, Google's BigTable, and other cloud-based databases are here already. The cloud isn't relational (yet) but this does represent an important shift away from SQL. It's true that Microsoft have promised SQL support in their Azure services platform but I expect SQL won't be the only way to access data. I'd be surprised if it was.

    The mainstream database vendors do recognise the need to move away from the constraints of SQL and they are doing things about it. Microsoft MDX, LINQ and the Entity Data Model are some evidence of that. There have been some relational database experiments as well and lots of open source efforts but none that are mainstream yet.

    There will be more of the same but admittedly there is a mountain to climb because of the huge vested interests in SQL. However, I concluded long ago that advocating reform of SQL or building better SQL DBMSs or rallying around the ISO SQL Standard is no solution at all. SQL never lived up to the relational model, doesn't meet modern needs and is broken. We should move on.

    In my opinion it is more important than ever not to keep mis-associating SQL with the relational model itself. For example the following group are responding to some very valid issues with database systems but some of them mistakenly assume that their problems are due to the relational model. I don't want to see the relational model dragged down by SQL and I expect you don't either.

    Notes from the NoSQL Meetup (Yahoo! Developer Network Blog)

  9. #9
    Join Date
    Jun 2009
    Posts
    66
    Linq and EF are both "generating sql" underneath, their application tools to help dev faster, I don't think it's designed to "move away" from SQL (although it can use alternate datasources like xml)

    How can cloud-based databases be relational? Based on what I read, it's awful. Constraints have to be done on the application side. There is no "structure" (One entity may have more attributes than the next entity). It's practically "EAV" in disguise (don't forget the latencies)

    I agree that SQL is broken and especially the "rdbms implementation" as well. However, I think we are trying to replace SQL with another broken SQL 2.0 (cloud based databases).

    Why not use Tutorial D or Codd's alpha language and build a proper implementation that adheres to the relational model?
    Last edited by sqlguru; 07-08-09 at 10:00.

  10. #10
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by sqlguru
    Linq and EF are both "generating sql" underneath, their application tools to help dev faster, I don't think it's designed to "move away" from SQL (although it can use alternate datasources like xml)
    LINQ is a data access paradigm in its own right. It doesn't need SQL even though SQL data access is obviously its principal use today.

    Quote Originally Posted by sqlguru
    I agree that SQL is broken and especially the "rdbms implementation" as well. However, I think we are trying to replace SQL with another broken SQL 2.0 (cloud based databases).
    A cloud database isn't any kind of model, it's just one use to which a suitable model can be put. The point is that we need better models than SQL and given time I fully expect that the relational model will become one of them.

    Quote Originally Posted by sqlguru
    Why not use Tutorial D or Codd's alpha language?
    Tutorial D is designed for educational use and Alpha has never been implemented as far as I know. Date and Darwen are working on an "Industrial D" relational language, which hopefully will be more suitable for the mainstream. IBM and Microsoft Research have done similar projects too but they haven't got implemented.

    I recommend you try Rel, at least as an educational and experimental tool.
    Rel

  11. #11
    Join Date
    Jun 2009
    Posts
    66
    The relational model is already here but is implemented wrong (both the language and implementation). The wrong implementation and non-relational language (sql) is even accepted.

    Cloud databases can never be the implementation for the relational model because there is no concept of tables, domains, constraints etc, it's just a key/value data dump, according to amazon's simpledb concept.

    Language is only part of it, the other part is the implementation. Industrial D will only be as practical as it's implementation.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by sqlguru
    I agree that SQL is broken
    Wow. This will be heart-breaking news to all the clients running database applications I developed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by sqlguru
    Cloud databases can never be the implementation for the relational model because there is no concept of tables, domains, constraints etc, it's just a key/value data dump, according to amazon's simpledb concept.
    That's Amazon's concept though. Cloud databases certainly could implement the relational model and that may happen very soon - possibly even in Microsoft Azure for example.

  14. #14
    Join Date
    Jun 2009
    Posts
    66
    Quote Originally Posted by dportas
    That's Amazon's concept though. Cloud databases certainly could implement the relational model and that may happen very soon - possibly even in Microsoft Azure for example.
    So when cloud databases implement the relational model, what's the difference between a in-house true rdbms vs relational cloud database? I can see massive latencies, difficult administration, tedious maintenance for the cloud database. From what I read about amazon's simpledb, when you put data in a node, it takes time for it to replicate to other nodes. How is this different from current replication? The only benefit I see from cloud databases are the cost factor.

    I don't think cloud databases will implement the relational model anytime soon, if it does this properly....it's just a externally hosted rdbms.

  15. #15
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by blindman
    Wow. This will be heart-breaking news to all the clients running database applications I developed.
    If they are using SQL then they are certainly living with its problems. For example they can write a query against your databases and get duplicate rows and duplicate columns or results without keys or unique column names. Also queries which contain nulls and where the treatment of those nulls is inconsistent in different parts of the query or wrong according to everyday maths, logic or reality. They are using a language that lacks the type-safety and type-extensibility taken for granted by every other modern programming language. Also they are using a DBMS that can't implement referential integrity or enforce many of the business rules on which their organisation depends. And that's just for starters.

    Even if they manage to avoid or ignore these things they have probably still had to pay Microsoft or Oracle or some other vendor to build them into the product, just because such "features" are part of the SQL standard. So they are paying for complexity and potential weaknesses that are totally unnecessary.

    If they truly aren't aware of any of these issues then they probably haven't been using SQL for long enough!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •