[thelist] yet another database/table relationship design question

Paul Cowan evolt at funkwit.com
Thu Oct 17 22:39:01 CDT 2002


the rudytron 9000 wrote:
> the foreign key sits in the "many" table, and points to the "one" table
>
> so it's not a foreign key from Product to Journal, rather, it's a foreign
> key from Journal to Product
>
> similarly, it's a foreign key from Ancillaries to Product

graaargh! I wish I had kept the first draft of that paragraph, before I
re-wrote it to make it clearer. I had it the other way around, I swear --
in fact, not the other way around, but worded differently, which was less
ambiguous (but poorly written, hence the re-write).

However, you're right, it is a common mistake, and one easily made -- I
have been pulled up on that one before. It does, perhaps, seem intuitive
to say "FROM parent TO child", rather than the other way around, which
might lead to these sorts of problems. My bad.


then, elsewhere, he wrote:
> the only thing you have left to learn to become an sql guru, is when
> to use a query, the raw child, when to use a view, the adolescent, and
> when to use a stored procedure, the lover, the mysterious, the
> all-powerful, the inescapable...

I'm printing this out. Fantastic. Stored procedures rule.

Also, I heartily approve of your use of "an sql" rather than "a sql".
Truly wise are you in the ways of data.

I'm going OT, so:

<tip type="SQL Server" author="Paul Cowan">
Just because system stored procs start with sp_, doesn't mean you should
call yours that. In fact, DON'T. Ever.

Why? Apart from the fact that it makes it unclear which are system and
which are user, it's a performance hit. *Every single time* such a
proc is executed, you'll have an additional cache miss as SQL Server
tries to find the proc. This is a small performance hit, but who
wants ANY performance hit when they don't need one?
</tip>

Paul






More information about the thelist mailing list