Re: Can FK be nullable/optional by design?

From: Tobin Harris <>
Date: Fri, 12 Dec 2003 21:50:47 -0000
Message-ID: <brddal$26unq$>

"Bob Badour" <> wrote in message
> "Tobes (Breath)" <> wrote in message
> news:brck8d$1t2ru$
> >
> > "Joe "Nuke Me Xemu" Foster" <joe_at_bftsi0.UUCP> wrote in message
> >
> > > Did you really mean to claim that ALL non-nullable attributes MUST
> > > 'logically' be included as part of the primary key?!
> >
> > Well, not really! I was just throwing in another option - where if the
> > existance of one entity is dependent on another, then you can make the
> of
> > that entity part of a composite key in the dependent entity. It's an
> > alternative to just non nullable foreign keys, where the related
> > become part of a primary key, rather than just a foreign key. Sorry, I
> think
> > I need to take my anti-waffle pill, can't seem to put a good explanation
> > together 8-)


> Please allow me to hang an important point off of your post. The bind you
> find yourself in above is certainly not unique to you so there is no need
> take this personally.
> Your bind above demonstrates a very real pitfall of confusing knowledge of
> specific tool with knowledge of fundamentals. I have seen numerous people
> fall into this specific pit throughout my career. I figure at least a 90%
> chance the tool you know is Erwin, and you are describing their
> "identifying" vs. "non-identifying" relationships.

Interestingly, I have used Erwin, but only briefly! My knowledge of this technique came from something tought in relational theory during my degree. Basically, we were being shown how to transition from conceptual ER diagrams to a physical model, and this specific technique was to be used if one entity's existance was dependent on another. I even recall the classroom example! This was along the lines of if you had the entities Cinema and CinemaScreen, then the existance of the screen might be dependent on the cinema (no screen without a cinema kinda thing). Therefore, the PK of the cinema would 'propogage' down to form part of the CinemaScreens PK. I'm not really bothered about the context, this just did seem like a logical thing to do.

Don't worry, I haven't taken this personally! However, having learnt this approach well before sitting down and trying to use a RDBMS, I found that when using any RDBMS, they seemed to support the concept of a column that is part of a primary key, and a foreign key also. So, way back then I never questioned it.

> I have seen people using this tool create schemas with ridiculous six and
> seven part compound primary keys and call it "normalization".

Yeah, I've fallen into this trap once or twice (although not quite so far!)

> Your bind above also demonstrates the dangers of using a graphical crutch
> place of real thought and analysis.


> I respectfully suggest you will find yourself much more effective if you
> learn the fundamentals before the tools.

A fair suggestion, although I thought I knew at least most of the fundamentals! I've always put learning this before learnign the tools. That way, when you come to learn the tools, it os interesting to see if/how they supported the things you want to achieve, rather than pushing buttons seeing what the tool could do, and then trying to understand it!

Just out of interest, what would you describe as the fundamentals?

Tobes Received on Fri Dec 12 2003 - 22:50:47 CET

Original text of this message