473,847 Members | 1,445 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Can FK be nullable/optional by design?

Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.

It works, and it is simpler.
The design that satisfy business rule and FK not null can be
implemented but it will be more complicated.

Example: There are clients. A client might belong to only one group.

Case A.
Group(GroupID PK, Name,Code…)
Client(ClientID PK, Name, GroupID FK NULL)
Case B(more cleaner)
Group(GroupID PK, Name, GroupCode…)

Client (ClientID PK, Name, ….)
Subtype:
GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

There is one more entity in Case B and it will require an additional
join in compare with caseA
Example: Select all clients that belongs to any group
Summary Q: Is it worth to go with CaseB?

Thank you in advance
Jul 20 '05 #1
20 4903
"Andy" <ne********@hot mail.com> wrote in message <news:ed******* *************** ****@posting.go ogle.com>...
Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.
Nulls suck. Dealing with Null is ugly any way you look at it.
It works, and it is simpler.
The design that satisfy business rule and FK not null can be
implemented but it will be more complicated.

Example: There are clients. A client might belong to only one group.

Case A.
Group(GroupID PK, Name,Code.)
Client(ClientID PK, Name, GroupID FK NULL)
In this scheme, a client may belong to no group or one group but
cannot belong to more than one group. Is this the business rule?
Case B(more cleaner)
Group(GroupID PK, Name, GroupCode.)

Client (ClientID PK, Name, ..)
Subtype:
GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

There is one more entity in Case B and it will require an additional
join in compare with caseA
Example: Select all clients that belongs to any group
With one tweak, GroupedClient can be a many<->many link between
Client and Group. Otherwise, you can always use a view to turn
Case B into Case A for the convenience of a particular program.
Summary Q: Is it worth to go with CaseB?


Case C. Use one or more "special" groups to "contain" otherwise
"groupless" clients. However, you now have the "special" groups
to deal with.

--
Joe Foster <mailto:jlfoste r%40znet.com> Sign the Check! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
Jul 20 '05 #2
ne********@hotm ail.com (Andy) writes:
General statement: FK should not be nullabe to avoid orphans in DB.


I don't see the reasoning behind this statement. Any column that
references keys to another table should be explicitly specified as such
to avoid orphans.

If that column may sometimes be unknown/unspecified for perfectly valid
records, I see no reason not to make it nullable.

--
"Notwithstandin g fervent argument that patent protection is essential
for the growth of the software industry, commentators have noted
that `this industry is growing by leaps and bounds without it.'"
-- US Supreme Court Justice John Paul Stevens, March 3, 1981.
Jul 20 '05 #3
depends on what a Group is and how it is used...

e.g.,
is a Group a Super-Client? -- individual Clients may be subsidiaries of a
Super-Client?
is a Group in internal designation, like a Sales territory?

How many Clients are there likely to be w/o a group?
When you need to act on the clients that are grouped, do you also need to
act on the clients that are not grouped?

[ps. in Case B, where did PersonID come from? Is that the Client?]
Example: There are clients. A client might belong to only one group.
Case A.
Group(GroupID PK, Name,Code.)
Client(ClientID PK, Name, GroupID FK NULL)
Case B(more cleaner)
Group(GroupID PK, Name, GroupCode.)

Client (ClientID PK, Name, ..)
Subtype:
GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

There is one more entity in Case B and it will require an additional
join in compare with caseA
Example: Select all clients that belongs to any group
Summary Q: Is it worth to go with CaseB?

Thank you in advance

Jul 20 '05 #4
"Trey Walpole" <tr********@SPc omcastAM.net> wrote in message news:<u3******* *******@tk2msft ngp13.phx.gbl>. ..
depends on what a Group is and how it is used...

e.g.,
is a Group a Super-Client? -- individual Clients may be subsidiaries of a
Super-Client?
is a Group in internal designation, like a Sales territory?

How many Clients are there likely to be w/o a group?
When you need to act on the clients that are grouped, do you also need to
act on the clients that are not grouped?

[ps. in Case B, where did PersonID come from? Is that the Client?]


Yes, it does.
It should be this way

[ps. in Case B, where did PersonID come from? Is that the Client?]

Case B
Group(GroupID PK, Name, GroupCode.)
Client (ClientID PK, Name, ..)
Subtype:
GroupedClient (ClientID PK/FK, GroupID FK NOT NULL)
Jul 20 '05 #5
ne********@hotm ail.com (Andy) wrote in message news:<ed******* *************** ****@posting.go ogle.com>...
Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.


Where did this statement come from? The idea of an orphan belongs to
network and hierarchical databases (old fashioned) or to
object-oriented databases (allegedly new), where the only way to get
to a record might be through its parent record. In a relational
database there is no such thing as an orphan.

You can find your "orphans" by some equivalent of (client where
groupcode not present) (worded that way to keep away from arguments
about NULLS).

In your example, what you have is

A client may be a member of at most one group.

If you meant to have

A client must be a member of exactly one group.

then (in your example) you would have to use NOT NULL.

Regards,

Eric
Jul 20 '05 #6

"Andy" <ne********@hot mail.com> wrote in message
news:ed******** *************** ***@posting.goo gle.com...
Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.


I'm not too hot on all this, but here is what I was lead to believe: If
Client *must* belong to at least one group, then the client is dependent on
the group - it cannot exist without it. Therefore, it's primary key would
(at least logically) be a composite, where the group pk forms part of the
clients composite primary key. This would ensure that a client cannot exist
without a group!?

This might look like:
Client(GroupID PK, ClientID PK, Name )
Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)

Just my 2 pennies worth 8-)

Tobes

Jul 20 '05 #7
"Tobin Harris" <to************ ********@breath email.net> wrote in message <news:br******* *****@ID-135366.news.uni-berlin.de>...
"Andy" <ne********@hot mail.com> wrote in message
news:ed******** *************** ***@posting.goo gle.com...
Hi All!

General statement: FK should not be nullabe to avoid orphans in DB.

Real life:
Business rule says that not every record will have a parent. It is
implemented as a child record has FK that is null.
I'm not too hot on all this, but here is what I was lead to believe: If
Client *must* belong to at least one group, then the client is dependent on
the group - it cannot exist without it. Therefore, it's primary key would
(at least logically) be a composite, where the group pk forms part of the
clients composite primary key. This would ensure that a client cannot exist
without a group!?

This might look like:
Client(GroupID PK, ClientID PK, Name )
Did you really mean to claim that ALL non-nullable attributes MUST
'logically' be included as part of the primary key?!
Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)


This would avoid the null nonsense until someone does an outer join.

--
Joe Foster <mailto:jlfoste r%40znet.com> L. Ron Dullard <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
Jul 20 '05 #8

"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUC P> wrote in message
news:10******** *******@news-1.nethere.net.. .
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 PK 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 column(s)
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-)
Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)


This would avoid the null nonsense until someone does an outer join.


That's true. So which option would you go for?

Tobes
--
Joe Foster <mailto:jlfoste r%40znet.com> L. Ron Dullard <http://www.xenu.net/> WARNING: I cannot be held responsible for the above They're coming to because my cats have apparently learned to type. take me away,

ha ha!
Jul 20 '05 #9
"Tobes (Breath)" <to************ ****@breathemai l.net> wrote in message <news:br******* *****@ID-131901.news.uni-berlin.de>...
"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUC P> wrote in message
news:10******** *******@news-1.nethere.net.. .
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 PK 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 column(s)
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-)


The ClientID by itself should probably be the primary key, though
the GroupID could be made part of an alternate candidate key.
Otherwise, if the Client could optionally belong to one Group, the
relationship would be captured in a link table, as you suggested in B?

GroupedClient (PersonID PK/FK, GroupID FK NOT NULL)


This would avoid the null nonsense until someone does an outer join.


That's true. So which option would you go for?


Maybe have a special "Loners" group? =) It's hard to say given
the information at hand. Yeah, I know, the usual cop-out...

--
Joe Foster <mailto:jlfoste r%40znet.com> Sacrament R2-45 <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
5975
by: ESPNSTI | last post by:
Hi, Please don't shoot me if the answer is simple, I'm new to .Net and C# :) .. I'm attempting to convert a nullable type to it's "non-nullable" type in a generic way (without knowing what specific type the nullable type is.) The reason I'm trying this is because when I attempt to pass a nullable type value to a SqlCommand parameter and then attempt to execute it I the following error: "No mapping exists from object type...
27
5645
by: Yuriy Solodkyy | last post by:
Hi VS 2005 beta 2 successfully compiles the following: using System; using System.Collections.Generic; using System.Text; namespace ConsoleApplication1 { class Program {
5
2057
by: Kevin Yu | last post by:
hi all since the DateTime can't be assign null, the min value is set to 1901 or something, if in a application design, the date field can be null, so in between the UI and the DB, the business logic has to take care of the conversion?? e.g when inserting DateTime.minvalue into db, (especially when using store precedure, it seems that pass null parameter to a store precedure cause problem.), the store procedure must translate the minvalue...
3
2105
by: rubikzube* | last post by:
Hi. I'm trying to write a snippet of code to detect if a variable is a nullable<t> struct, and I can't figure out the right syntax or if such a thing is even possible... Below are the results that I got when I attempted to perform some simple tests. Nullable<int> i = 32; bool isNullableClass = i is Nullable; // false
1
5685
by: Joe Bloggs | last post by:
Hi, Can someone please kindly show me how to determine if a type (read value type) is Nullable. MSDN has this KB: How to: Identify a Nullable Type (C# Programming Guide) http://msdn2.microsoft.com/en-us/library/ms366789.aspx however, using their code snippet, I couldn't get it to work:
5
51699
by: GG | last post by:
I am trying to add a nullable datetime column to a datatable fails. I am getting exception DataSet does not support System.Nullable<>. None of these works dtSearchFromData.Columns.Add( new DataColumn( "StartDate", typeof( DateTime? ) ) ); dtSearchFromData.Columns.Add( new DataColumn( "EndDate", typeof( System.Nullable<DateTime>) ) ); Any ideas?
8
10708
by: Sam Kong | last post by:
Hello, I want to define a generic class which should accept only nullable types or reference types. What's the best way to costrain it? --------- class MyClass<T>{ ...
1
2510
by: lord.zoltar | last post by:
I was asked to make a little "Yes/No" radio button for a project. There was a small wrinkle, that the user might want to have NO button checked. This didn't seem so bad, so I made YesNoValue property for the control and I made it a Nullable(Of Boolean). Now a problem occurrs when I add this control to a form and try to view the form in design view. The warning I get is: "Method 'System.Nullable`1.op_Implicit' not found." When I dive into...
20
2438
by: sarnold | last post by:
Hello, I am proposing to create two new keywords for C, 'notnull' and 'nullable'. Their meaning is a sort of 'design by contract', ensuring some rules are verified at compile-time. It should not require additional runtime- checks to be implemented. I have posted my description of how it should work at this URL:
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9734
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10991
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10653
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7888
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4540
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4129
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3168
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.