Post reply

Table Theory - In Table Row Versioning - Questions - HELP!?!?

  • June 9, 2005 at 6:07 am

    #564158

    In my previous posted, te create table statements for the versions has the identity property specified. This is incorrect and only the core table should have the identity property.

    SQL = Scarcely Qualifies as a Language

  • June 9, 2005 at 7:00 am

    #564184

    Boy, you eat just one little baby and everyone thinks you are a monster!

    Interesting new facette. I always thought you only stomp on the flower on your way.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 9, 2005 at 7:02 am

    #564186

    I don't think you're a Monster Joe. You just have a way to be very direct about what you think... and I like that .

  • June 9, 2005 at 7:08 am

    #564189

    Hi Joe,

    I was wondering what you meant by 'IDENTITY is both a proprietary data type and never a key; BIT is a proprietary data type;'

    Would you not use an IDENTITY int as a PK ?

    cheers

    dbgeezer

  • June 9, 2005 at 7:13 am

    #564191

    I'm curious to see what he's gonna answer to this. But here's one of his answer on a very similar topic :

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=172998

  • June 9, 2005 at 7:13 am

    #564190

    Would you not use an IDENTITY int as a PK ?

    That's a joke, right?

    Please not again such a discussion. The fora here and the Google groups are full of them. Results? IMHO, do as you pleased...

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 9, 2005 at 7:14 am

    #564192

    Come on Frank.. we haven't had a holly war in so long .

  • June 9, 2005 at 7:24 am

    #564200

    I see what you mean.

    I do kind of sympathise and do agree totally that keys should be part of the entity they represent but could see the argument become indefensible in a commercial environment where speed is the god. A lot of our tables have key values that are ID ints as the 'proper' PK would be a combination of 2 fields, one  a varchar(20), the other a varchar(2). Indexing this when iterating over 200 million + rows would be a problem perhaps.

    I don't want to start ANY kind of discussion about this as I already agree with you about the design issues.

    I loved the 'kill the guy who designed it' post....

    cheers

    dbgeezer

  • June 9, 2005 at 7:31 am

    #564202

    That's why it's entertaining to read his thaughts, and very instructionnal too.

  • June 9, 2005 at 9:10 am

    #564255

    So would you go for a key of a varchar(20) then and write a custom encoding sheme to enable it to be indexed quickly (as I believe the GUIDs in SQL server do) ...

    Have you used this methodology in a high volume, business critical area ?

    I'm not questioning your argument as I do agree totally but am puzzled as to how this gets past the bean counters.

    cheers

    dbgeezer

  • June 9, 2005 at 10:17 am

    #564292

    To play the devil's advocate here, I would like to add a different point of view. I work for a company that does insurance auditing, and in this business you absolutely positively must maintain historical records for the "state" of data at different times. Specifically, when I receive data from my clients, I must:

    - maintain it in its original state (when first received)

    - record changes to the data (when received again - an update)

    - maintain what state the data is currently in

    This is the classic "current" vs. "history" argument. I think the only way to sanely handle this is to maintain two separate tables, one for current data, and one for history data. The current data should have referential integrity maintained, and the history table should have RI maintained if it is POSSIBLE, but most likely it will not be. To maintain RI for the "history" implies that any tables that this history table depends on also maintain "current" vs. "history" data as well, but as far as I'm concerned, it's something you have to live with.

    If you think about it, you are basically building a data warehouse as soon as you build "history" tables, and "current" is your data mart/operational data.

    By the way, this discussion seems to mainly be about temporal databases. I don't specifically know of a DBMS which is built to exclusively be a temporal database, but if you google "temporal database" you will find a lot of info about them, including concepts for building temporal relational databases.

    http://www.google.com/search?hl=en&q=temporal+database&btnG=Google+Search

    Hope that helps some!

    -Jon

    Update- Check out this article at Wikipedia for some good information on temporal databases: http://en.wikipedia.org/wiki/Temporal_database

  • June 9, 2005 at 1:02 pm

    #564358

    Speaking of temporal data, you can also check out Richard Snodgrass' homepage. You can download his famous book on temporal data there for free. It's a must-read, to me anyway.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • June 9, 2005 at 4:23 pm

    #564424

    Thanks to you all. Not only did I get a line on some good new books, but it looks like the Archive/History table model will be used for versioning, not this in-table versioning model - a great relief for the DBAs involved.

  • June 9, 2005 at 4:26 pm

    #564426

    Happy to hear we got through your programmers .

  • June 10, 2005 at 11:09 am

    #564688

    Hey now - don't diss the programmers. After all, I are one as well

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply