Re: Problems with set_sql and iterators

[prev] [thread] [next] [Date index for 2005/06/20]

>>>>> "Jacinta" == Jacinta Richardson <jarich@xxxxxxxxxxxx.xxx.xx> writes:


Jacinta> You have highlighted one problem in my spec, and that is that I should have:

Jacinta> 	# Declare our primary key field(s)
__PACKAGE__-> columns(Primary => 'StaffID', 'ProjectName');
__PACKAGE__-> columns(All => 'StaffID', 'ProjectName', 'Allocation');
__PACKAGE__-> columns(TEMP => 'Total');

Jacinta> as we don't want the staff member allocated to the same project twice,
Jacinta> with 2 different allocations, but this still doesn't solve the problem.

Then what you really need is a unique column for each allocation, and
a constraint to ensure that there are no duplicates.  Especially if
you think there might be NULLs in your "primary key", which means it's
no longer a real primary key in a real database.

Jacinta> The fact that I *know* the search returns data, can access that data,
Jacinta> but can't iterate over seems to be a problem.  It seems to me that
Jacinta> either object creation without defined primary keys should fail (so
Jacinta> failing at the search stage) or that both iteration and array contexts
Jacinta> should work.

I'd say it's a bug that CDBI lets you define a broken database (NULL
in a primary key), but CDBI has to trust that you have some
responsibility in the matter to reflect a valid reality.

Jacinta> Upon more thought I guess I'm challenging the idea that an object
Jacinta> whose primary key is undefined but otherwise contains data should be
Jacinta> considered false.  I'm struggling to find any situation that *I* would
Jacinta> expect that result.  I'm sure that there situations, so if you could
Jacinta> expand on one or two that would be appreciated.

Because primary keys never contain NULL.

O'Reilly has just released a *great* easy-to-read book on database
design by returning to the set theoretic principles (but using small
words so that even *I* understood), called:

        "Database in Depth: Relational Theory for Practioners",
        by C J Date

Those of you that have been around the block recognize Date as having
worked with E.F.Codd, the founder of all this stuff.  (And for whom
we named the "Date" type, but I digress. :)

I highly recommend this book, for you in particular Jacinta, and for
everyone who has to be a part-time or full-time DBA.  I know my
clients are already picking up copies.

And this isn't just "how to go from 2NF to 3NF".  This is *why* we do
that, and also why there should never be any duplicate rows in a
table, and even why NULL is a bad idea (although we'd need to
normalize tables an additional step to get rid of them).

        -- 
        Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
<merlyn@xxxxxxxxxx.xxx> <URL:http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl training!