|
|
You're in the
sql server (alternate)
group:Can FK be nullable/optional by design?
sql server (alternate):
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?
[quoted text, click to view]
"Andy" <net__space@hotmail.com> wrote in message <news:edb90340.0311301114.19718061@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. [quoted text, click to view] > 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? [quoted text, click to view] > 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. [quoted text, click to view] > 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!
net__space@hotmail.com (Andy) writes:
[quoted text, click to view] > 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.'"
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?] [quoted text, click to view] > 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
[quoted text, click to view]
"Trey Walpole" <treyNOpole@SPcomcastAM.net> wrote in message news:<u3p24vCuDHA.3144@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:
[quoted text, click to view]
net__space@hotmail.com (Andy) wrote in message news:<edb90340.0311301114.19718061@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,
[quoted text, click to view]
"Tobin Harris" <tobin_dont_you_spam_me@breathemail.net> wrote in message <news:braub1$1cceh$1@ID-135366.news.uni-berlin.de>... > "Andy" <net__space@hotmail.com> wrote in message > news:edb90340.0311301114.19718061@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. > 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?! [quoted text, click to view] > 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!
[quoted text, click to view] "Andy" <net__space@hotmail.com> wrote in message news:edb90340.0311301114.19718061@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. > 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
[quoted text, click to view]
"Tobes (Breath)" <tobin_dont_spam_me@breathemail.net> wrote in message <news:brck8d$1t2ru$1@ID-131901.news.uni-berlin.de>... > "Joe "Nuke Me Xemu" Foster" <joe@bftsi0.UUCP> wrote in message > news:1071189386.456990@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. [quoted text, click to view] > > > 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!
[quoted text, click to view]
"Tobes (Breath)" <tobin_dont_spam_me@breathemail.net> wrote in message news:brck8d$1t2ru$1@ID-131901.news.uni-berlin.de... > > "Joe "Nuke Me Xemu" Foster" <joe@bftsi0.UUCP> wrote in message > news:1071189386.456990@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.
Just a couple of things:
[quoted text, click to view] > 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. [quoted text, click to view] > 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. [quoted text, click to view] > 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 :) [quoted text, click to view] > 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 (drsql@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 :) [quoted text, click to view] "Bob Badour" <bbadour@golden.net> wrote in message news:Vf6dnepaArIqnkeiRVn-tw@golden.net... > "Tobes (Breath)" <tobin_dont_spam_me@breathemail.net> wrote in message > news:brck8d$1t2ru$1@ID-131901.news.uni-berlin.de... > > > > "Joe "Nuke Me Xemu" Foster" <joe@bftsi0.UUCP> wrote in message > > news:1071189386.456990@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. > >
[quoted text, click to view] "Joe "Nuke Me Xemu" Foster" <joe@bftsi0.UUCP> wrote in message news:1071189386.456990@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-) [quoted text, click to view] > > 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 [quoted text, click to view] > -- ha ha!> 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,
[quoted text, click to view]
"Tobin Harris" <tobin_dont_you_spam_me@breathemail.net> wrote in message news:brddal$26unq$1@ID-135366.news.uni-berlin.de... > "Bob Badour" <bbadour@golden.net> wrote in message > news:Vf6dnepaArIqnkeiRVn-tw@golden.net... > > "Tobes (Breath)" <tobin_dont_spam_me@breathemail.net> wrote in message > > news:brck8d$1t2ru$1@ID-131901.news.uni-berlin.de... > > > > > > "Joe "Nuke Me Xemu" Foster" <joe@bftsi0.UUCP> wrote in message > > > news:1071189386.456990@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. [quoted text, click to view] > 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. [quoted text, click to view] > > 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?
[quoted text, click to view]
"Bob Badour" <bbadour@golden.net> wrote in message news:Vf6dnepaArIqnkeiRVn-tw@golden.net... > "Tobes (Breath)" <tobin_dont_spam_me@breathemail.net> wrote in message > news:brck8d$1t2ru$1@ID-131901.news.uni-berlin.de... > > > > "Joe "Nuke Me Xemu" Foster" <joe@bftsi0.UUCP> wrote in message > > news:1071189386.456990@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. [quoted text, click to view] > 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!) [quoted text, click to view] > 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
[quoted text, click to view]
"Bob Badour" <bbadour@golden.net> wrote in message news:tPGdndKS74g91Eei4p2dnA@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?
[quoted text, click to view]
"Bob Badour" <bbadour@golden.net> wrote in message <news:tPGdndKS74g91Eei4p2dnA@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. =/ [quoted text, click to view] > 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!
[quoted text, click to view] "Bob Badour" <bbadour@golden.net> wrote in message news:tPGdndKS74g91Eei4p2dnA@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. [quoted text, click to view] > 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. [quoted text, click to view] > 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
[quoted text, click to view]
"Tobin Harris" <tobin_dont_you_spam_me@breathemail.net> wrote in message news:brnvmp$5gqn4$1@ID-135366.news.uni-berlin.de... > > "Bob Badour" <bbadour@golden.net> wrote in message > news:tPGdndKS74g91Eei4p2dnA@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.
[quoted text, click to view]
"Tobes (Breath)" <tobin_dont_spam_me@breathemail.net> wrote in message news:brq3iu$5nfbc$1@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 (drsql@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 :)
[quoted text, click to view] "Bob Badour" <bbadour@golden.net> wrote in message news:aoCdnbmkVbe0SUKiRVn-tA@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... [quoted text, click to view] > 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
[quoted text, click to view]
"Tobes (Breath)" <tobin_dont_spam_me@breathemail.net> wrote in message news:brq3iu$5nfbc$1@ID-131901.news.uni-berlin.de... > > "Bob Badour" <bbadour@golden.net> wrote in message > news:aoCdnbmkVbe0SUKiRVn-tA@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. [quoted text, click to view] > > 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. [quoted text, click to view] > 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).
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |