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

[John Sheppard]

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

[Tom van Stiphout]

On Fri, 2 May 2008 14:19:57 +1000, "John Sheppard" <sp**@nospam.com>
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=UnknownBadStuffLeftFromEarlierDays

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

[--CELKO--]

>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.

[aj]

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.

[--CELKO--]

>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.