473,836 Members | 1,438 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
20 4900
"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-)


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 to
take this personally.

Your bind above demonstrates a very real pitfall of confusing knowledge of a
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
"identifyin g" vs. "non-identifying" relationships.

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

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

I respectfully suggest you will find yourself much more effective if you
learn the fundamentals before the tools.
Jul 20 '05 #11
Just a couple of things:
Your bind above demonstrates a very real pitfall of confusing knowledge of a 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
"identifyin g" vs. "non-identifying" relationships.
Identifying and non-identifying relationships are not an Erwin thing. They
are an idef1x thing. Check FIPS publication 184:
http://www.itl.nist.gov/fipspubs/idef1x.doc.
I have seen people using this tool create schemas with ridiculous six and
seven part compound primary keys and call it "normalization" .
Just because you have six and seven part compound keys does not mean that
you are not normalized. It may take that many different atomic bits to
uniquely identify something. If these compound keys are built from six
relationships, the chances of it being normalized are about as good as the
San Diego Chargers winning last years Super Bowl, but it is possible.
Your bind above also demonstrates the dangers of using a graphical crutch in place of real thought and analysis.
So you don't use data models? The graphical "crutch" as you call it is
pretty standard stuff. I have never considered data models controversial in
the least. Cannot question the need for thought and analysis though :)
I respectfully suggest you will find yourself much more effective if you
learn the fundamentals before the tools.
You are correct (cannot believe I am agreeing with you :) about just having
tool knowledge. Erwin is a great tool, but they do have some
terminology/practices that are not standard, and frankly the tool will let
you get away with murder. It's job is to let you draw pictures of your
data, not to give you a hard time. That is your job Bob :)

--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail. com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"Bob Badour" <bb*****@golden .net> wrote in message
news:Vf******** ************@go lden.net... "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-)


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

to take this personally.

Your bind above demonstrates a very real pitfall of confusing knowledge of a 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
"identifyin g" vs. "non-identifying" relationships.

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

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

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

Jul 20 '05 #12
"Bob Badour" <bb*****@golden .net> wrote in message
news:Vf******** ************@go lden.net...
"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-)


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

to take this personally.

Your bind above demonstrates a very real pitfall of confusing knowledge of a 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
"identifyin g" 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 in 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

Jul 20 '05 #13
"Tobin Harris" <to************ ********@breath email.net> wrote in message
news:br******** ****@ID-135366.news.uni-berlin.de...
"Bob Badour" <bb*****@golden .net> wrote in message
news:Vf******** ************@go lden.net...
"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-)


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 to
take this personally.

Your bind above demonstrates a very real pitfall of confusing knowledge
of a
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
"identifyin g" 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!
I doubt, then, you were actually taught any relational theory. With the
current state of the education, I do not find that surprising.

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.
The candidate keys and foreign keys within a relation are generally
independent of one another and can overlap. Of course, a correspondence
exists between a foreign key in a referencing relation and a candidate key
in the referenced relation. I said "generally independent" above because in
the case that a relation refers to itself, the foreign key and candidate key
are in the same relation.

Whether some or all of a foreign key forms some or all of a candidate key
has no particular importance to me.

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 in
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?


Chris Date's _Introduction to Database Management Systems_ makes a good
start at them. I would seem foolish to try to teach them in an email
message.

One would start with "What is data?" and "What does it mean to manage data?"
From there, one would move to: "What principles facilitate or guide
effective data management?" And onward...

Since you apparently think one can easily enumerate them in an email, what
would you describe as the fundamentals?
Jul 20 '05 #14
"Bob Badour" <bb*****@golden .net> wrote in message
news:tP******** ************@go lden.net...
I doubt, then, you were actually taught any relational theory. With the
current state of the education, I do not find that surprising. One would start with "What is data?"


If I add this data to that data do I have 2 datas?
Jul 20 '05 #15
"Bob Badour" <bb*****@golden .net> wrote in message <news:tP******* *************@g olden.net>...
I doubt, then, you were actually taught any relational theory. With the
current state of the education, I do not find that surprising.
At my alma mater, UCSB, relational theory was an elective, but
at least it was available at all. =/
Chris Date's _Introduction to Database Management Systems_ makes a good
start at them. I would seem foolish to try to teach them in an email
message.


I have the seventh edition. Is there a definitive list of the
changes made to the eighth, perhaps at http://dbdebunk.com/ ?

--
Joe Foster <mailto:jlfoste r%40znet.com> "Regged" again? <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 #16

"Bob Badour" <bb*****@golden .net> wrote in message
news:tP******** ************@go lden.net...
Chris Date's _Introduction to Database Management Systems_ makes a good
start at them. I would seem foolish to try to teach them in an email
message.
Don't worry Bob, I wasn't expecting you to seem foolish, or give a full
tutorial.
One would start with "What is data?" and "What does it mean to manage data?" From there, one would move to: "What principles facilitate or guide
effective data management?" And onward...
Ok, this makes sense.
Since you apparently think one can easily enumerate them in an email, what
would you describe as the fundamentals?


I hadn't even considered whether it was difficult or not. I was simply
interested in what your perceived "fundamenta ls" entailed, mainly so I could
go and learn more... I kind of expected you to mention some general topics,
which may or may not have included:

Normalization - learning how to extrapolate to 1st, 2nd and 3rd normal form
schemas
Integrety - learning that integrety applies at various levels - Domain,
Column, Table, Database (Referential)
Data Types - seen as sets of permissable values that enforce business rules
by constraining the data that is stored.
Top-Down Analysis - learning to identify entities and business rules by
reading existing documentation, verbal communication etc
Bottom Up Analysis - learning to derive and normalise attribute listings
Keys and Identity - different types and why
Jul 20 '05 #17
"Tobin Harris" <to************ ********@breath email.net> wrote in message
news:br******** ****@ID-135366.news.uni-berlin.de...

"Bob Badour" <bb*****@golden .net> wrote in message
news:tP******** ************@go lden.net...
Chris Date's _Introduction to Database Management Systems_ makes a good
start at them. I would seem foolish to try to teach them in an email
message.
Don't worry Bob, I wasn't expecting you to seem foolish, or give a full
tutorial.
One would start with "What is data?" and "What does it mean to manage

data?"
From there, one would move to: "What principles facilitate or guide
effective data management?" And onward...


Ok, this makes sense.
Since you apparently think one can easily enumerate them in an email, what would you describe as the fundamentals?


I hadn't even considered whether it was difficult or not. I was simply
interested in what your perceived "fundamenta ls" entailed, mainly so I

could go and learn more... I kind of expected you to mention some general topics, which may or may not have included:

Normalization - learning how to extrapolate to 1st, 2nd and 3rd normal form schemas
Integrety - learning that integrety applies at various levels - Domain,
Column, Table, Database (Referential)
Data Types - seen as sets of permissable values that enforce business rules by constraining the data that is stored.
Top-Down Analysis - learning to identify entities and business rules by
reading existing documentation, verbal communication etc
Bottom Up Analysis - learning to derive and normalise attribute listings
Keys and Identity - different types and why


Your list of "fundamenta ls" does not answer any of the questions "What is
data?", "What does it mean to manage data?" or "What principles facilitate
or guide effective data management?"

Of the items in your list above, integrity and data types are fundamental,
but your elaborations above are anything but fundamental.

One can come up with any number of taxonomies for integrity
constraints--Chris Date has published enough of them in his career. The
taxonomy I find most enlightening is: All integrity constraints constrain
variables. Integrity is fundamental because it is fundamental to the
manipulation function when managing data.

A data type does not enforce business rules--the integrity function of the
dbms does this. Data type is fundamental to computing and not only to data
management. A data type comprises both a set of values and a set of
operations on those values. With respect to the relational model, Date and
Darwen have observed that data types define what we can make statements
about, and relations make statements about them.
Jul 20 '05 #18

"Bob Badour" <bb*****@golden .net> wrote in message
news:ao******** ************@go lden.net...
Your list of "fundamenta ls" does not answer any of the questions "What is
data?", "What does it mean to manage data?" or "What principles facilitate
or guide effective data management?"
In that case I'd be interested in learning some of these fundamentals. I may
have to take myself to the library...
Of the items in your list above, integrity and data types are fundamental,
but your elaborations above are anything but fundamental.

One can come up with any number of taxonomies for integrity
constraints--Chris Date has published enough of them in his career. The
taxonomy I find most enlightening is: All integrity constraints constrain
variables. Integrity is fundamental because it is fundamental to the
manipulation function when managing data.

A data type does not enforce business rules--the integrity function of the
dbms does this. Data type is fundamental to computing and not only to data
management. A data type comprises both a set of values and a set of
operations on those values. With respect to the relational model, Date and
Darwen have observed that data types define what we can make statements
about, and relations make statements about them.


Hmmm, I thought Data Types (including UDTs) did enforce business rules, by
constraining the set of possible values that can be stored in a column
constrained to that type. If a business rule dictates that data of a certain
type must fall within a spefic range, for example, then by defining a type
that imposes this constraint, the business rule could be enforced by the
Data Type?

Thanks for your reply

Tobes
Jul 20 '05 #19
"Tobes (Breath)" <to************ ****@breathemai l.net> wrote in message
news:br******** ****@ID-131901.news.uni-berlin.de...
Hmmm, I thought Data Types (including UDTs) did enforce business rules, by
constraining the set of possible values that can be stored in a column
constrained to that type. If a business rule dictates that data of a certain type must fall within a spefic range, for example, then by defining a type
that imposes this constraint, the business rule could be enforced by the
Data Type?


The type of data type chosen is the first step in enforcing business rules.
Clearly if the business rule states this will be an integer between -100 and
100, then you first choose a datatype. In this case, you might go with a
smallint, or just an integer. Then you apply a check constraint. A proper
Domain or a User Defined Type will include the datatype and some of the
checking needed. If you chose a varchar for instance, the user would be
able to insert whatever into the column, unless you built more elaborate
checking into your column.
--
----------------------------------------------------------------------------
-----------
Louis Davidson (dr***@hotmail. com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)
Jul 20 '05 #20

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
5644
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
2056
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
5684
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
51687
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
10704
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
10835
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
10541
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...
0
10249
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7785
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
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5818
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4447
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
4007
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3108
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.