Orphaned tupels, should I clean or is it ok to leave?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • John Sheppard
    #1

    Orphaned tupels, should I clean or is it ok to leave?

    Hello there,

    I was wondering if anyone could give me some advice on orphaned rows and
    foreign key constraints.

    I am migrating a MS-Access database that didnt have any relations setup
    properly, consequently there are gazillions of orphaned tuples.

    If I was to leave these as they were and set constraints with NO CHECK is it
    likly that I am going to run into problems down the track?

    Some may shudder that I would consider not cleaning the data but we have a
    timeline and there is months of cleaning to do, its something I would prefer
    to do after we make our deadlines.

    Thank you
    John Sheppard


    Tags: None
    • Tom van Stiphout
      #2
      Re: Orphaned tupels, should I clean or is it ok to leave?

      On Fri, 2 May 2008 14:19:57 +1000, "John Sheppard" <spam@nospam.co m>
      wrote:

      I am a purist and would not allow bad data in my database. But I'm
      also a realist and your suggestion makes some sense. However, I have
      seen that the desire to clean up the data disappears as the app is
      taken into production and no immediate averse effects are noticed. You
      and I know that's just a matter of time...
      Also look at it this way. If I have Orders for a CustomerID that no
      longer exists, I have limited options:
      1: Do research in printed invoices in some filing cabinet and try to
      find the missing customer and recreate him. A time-consiming
      proposition.
      2: Delete the orphans. Most are old. None can be recovered easily.
      3: Assign all orphans to CustomerID=-1,
      CustomerName=Un knownBadStuffLe ftFromEarlierDa ys

      Personally I would keep the Access app for research purposes, and
      choose option 2. ¡No Pasarán!

      -Tom.

      >Hello there,
      >
      >I was wondering if anyone could give me some advice on orphaned rows and
      >foreign key constraints.
      >
      >I am migrating a MS-Access database that didnt have any relations setup
      >properly, consequently there are gazillions of orphaned tuples.
      >
      >If I was to leave these as they were and set constraints with NO CHECK is it
      >likly that I am going to run into problems down the track?
      >
      >Some may shudder that I would consider not cleaning the data but we have a
      >timeline and there is months of cleaning to do, its something I would prefer
      >to do after we make our deadlines.
      >
      >Thank you
      >John Sheppard
      >

        Comment

        • --CELKO--
          #3
          Re: Orphaned tupels, should I clean or is it ok to leave?

          >If I was to leave these as they were and set constraints with NO CHECK is it likly that I am going to run into problems down the track? <<

          oh yeah! The schema will fill with garbage and will choke. Someone
          will use the meaningless data for a report, etc.

            Comment

            • aj
              #4
              Re: Orphaned tupels, should I clean or is it ok to leave?

              I can't help but comment here.

              We're implementing a 3rd party CRM package, and the software treats the
              database like a big pile of stupid ascii files. There's no RI - no FK's
              in the tables. NULLs are allowed everywhere... When a PK is deleted,
              rather than cascade delete, it looks like any logical references to it
              are simply set to null..

              Its enough to drive a DBA completely freaking insane...

              Is this sort of thing common in CRM packages?

              aj

              --CELKO-- wrote:
              >>If I was to leave these as they were and set constraints with NO CHECK is it likly that I am going to run into problems down the track? <<
              >
              oh yeah! The schema will fill with garbage and will choke. Someone
              will use the meaningless data for a report, etc.

                Comment

                • --CELKO--
                  #5
                  Re: Orphaned tupels, should I clean or is it ok to leave?

                  >It is enough to drive a DBA completely freaking insane...Is this sort of thing common in CRM packages? <<

                  I don't know about CRM packages, but I worked for a company that did
                  the same kind of crappy coding in Configuration Management software.
                  I reviewed a municipal public works package which only had IDENTITY
                  columns for keys and absolutely no column constraints. I am no longer
                  amazed at the level of cowboy coding in packages.
                  >When a PK is deleted, rather than ON DELETE CASCADE, it looks like any logical references to it are simply set to NULL.. <<
                  Done manually, instead of with ON DELETE SET NULL? You probably don't
                  remember pre_RDBMS databases, but they often had a bit flag at the
                  start of a record to mark it as active or deleted. The reason that
                  records were located by physical position within a file or pointers of
                  some kind. You would do explicit garbage collection every now and
                  then to re-claim the disk space and to re-build the pointer chains and
                  links. Another form of flag for this purpose was to set a link to NIL
                  by traversing down a chain to the leaf nodes, then recursively back up
                  the chain to the parent record.

                    Comment

                    Previous template Next
                    Working...