Can FK be nullable/optional by design?

[Andy]

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

[Joe \Nuke Me Xemu\ Foster]

"Andy" <ne********@hotmail.com> wrote in message <news:ed**************************@posting.google. 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:jlfoster%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!

[Bruce Lewis]

ne********@hotmail.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.

--
"Notwithstanding 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.

[Trey Walpole]

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

[Andy]

"Trey Walpole" <tr********@SPcomcastAM.net> wrote in message news:<u3**************@tk2msftngp13.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)

[Eric Junkermann]

ne********@hotmail.com (Andy) wrote in message news:<ed**************************@posting.google. 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

[Tobin Harris]

"Andy" <ne********@hotmail.com> wrote in message
news:ed**************************@posting.google.c om...

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

[Joe \Nuke Me Xemu\ Foster]

"Tobin Harris" <to********************@breathemail.net> wrote in message <news:br************@ID-135366.news.uni-berlin.de>...

"Andy" <ne********@hotmail.com> wrote in message
news:ed**************************@posting.google.c om...

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:jlfoster%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!

[Tobes \(Breath\)]

"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUCP> 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:jlfoster%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!

[Joe \Nuke Me Xemu\ Foster]

"Tobes (Breath)" <to****************@breathemail.net> wrote in message <news:br************@ID-131901.news.uni-berlin.de>...

"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUCP> 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:jlfoster%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!

[Bob Badour]

"Tobes (Breath)" <to****************@breathemail.net> wrote in message
news:br************@ID-131901.news.uni-berlin.de...

"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUCP> 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
"identifying" 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.

[Louis Davidson]

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
"identifying" 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********************@golden.net... "Tobes (Breath)" <to****************@breathemail.net> wrote in message
news:br************@ID-131901.news.uni-berlin.de...

"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUCP> 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
"identifying" 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.

[Tobin Harris]

"Bob Badour" <bb*****@golden.net> wrote in message
news:Vf********************@golden.net...

"Tobes (Breath)" <to****************@breathemail.net> wrote in message
news:br************@ID-131901.news.uni-berlin.de...

"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUCP> 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
"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 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

[Bob Badour]

"Tobin Harris" <to********************@breathemail.net> wrote in message
news:br************@ID-135366.news.uni-berlin.de...

"Bob Badour" <bb*****@golden.net> wrote in message
news:Vf********************@golden.net...

"Tobes (Breath)" <to****************@breathemail.net> wrote in message
news:br************@ID-131901.news.uni-berlin.de...

"Joe "Nuke Me Xemu" Foster" <jo*@bftsi0.UUCP> 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
"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!
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?

[Mike MacSween]

"Bob Badour" <bb*****@golden.net> wrote in message
news:tP********************@golden.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?

[Joe \Nuke Me Xemu\ Foster]

"Bob Badour" <bb*****@golden.net> wrote in message <news:tP********************@golden.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:jlfoster%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!

[Tobin Harris]

"Bob Badour" <bb*****@golden.net> wrote in message
news:tP********************@golden.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 "fundamentals" 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

[Bob Badour]

"Tobin Harris" <to********************@breathemail.net> wrote in message
news:br************@ID-135366.news.uni-berlin.de...

"Bob Badour" <bb*****@golden.net> wrote in message
news:tP********************@golden.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 "fundamentals" 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 "fundamentals" 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.

[Tobes \(Breath\)]

"Bob Badour" <bb*****@golden.net> wrote in message
news:ao********************@golden.net...

Your list of "fundamentals" 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

[Louis Davidson]

"Tobes (Breath)" <to****************@breathemail.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 :)

[Bob Badour]

"Tobes (Breath)" <to****************@breathemail.net> wrote in message
news:br************@ID-131901.news.uni-berlin.de...

"Bob Badour" <bb*****@golden.net> wrote in message
news:ao********************@golden.net...

Your list of "fundamentals" 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...
Try to find a library with a copy of the ISO/IEC Standard Vocabularies for
Information Technology. A friend drew my attention to an article in IEEE
Compute called _The Great Term Robbery_ a few years ago; I found both that
article and the standard vocabularies very informative with respect to "What
is data?".

I have never found a succinct list of principles, and if anyone knows of
one, I would love to see it. Codd's 12 Rules embody a lot of principles he
did not name explicitly; although, logical identity, guaranteed access,
physical and logical independence are all principles. Certainly, the
principle of separating concerns applies to data management in several ways.
As a general principle, one prefers to minimize, centralize and automate any
need for highly specialized or arcane knowledge. One prefers to maximize the
portability of one's data. One prefers to make easy things easy and to make
likely errors difficult. One prefers to minimize the learning curve for
casual users. etc.

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.

Data types form part of the definition of some constraints, but the
integrity function of the dbms enforces constraints. What you suggest above
is similar to suggesting that legislation and street signs enforce traffic
laws. Police officers and the judiciary enforce traffic laws.

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 does not impose the constraint; the integrity function of the dbms
imposes the constraint. The type merely describes the constraint. For a very
long time, almost all constraints in commerical SQL dbmses were nothing more
than comments. One was allowed to express them, but the integrity function
of the dbms ignored them (if one can really claim an integrity function even
exists in that situation).