Newsgroups: comp.databases.theory Subject: Re: Database naming convention (yet another post of it, but a bit different) From: Pablo Sanchez References: <3e55b51a.0210081559.4ac4c1f9@posting.google.com> Message-ID: User-Agent: Xnews/5.04.25 NNTP-Posting-Host: newsfeeds2 Date: 9 Oct 2002 21:29:15 -0500 Lines: 144 X-Authenticated-User: $$yl_vltqb X-Comments: This message was posted through Newsfeeds.com X-Comments2: IMPORTANT: Newsfeeds.com does not condone, nor support, spam or any illegal or copyrighted postings. X-Comments3: IMPORTANT: Under NO circumstances will postings containing illegal or copyrighted material through this service be tolerated!! X-Report: Please report illegal or inappropriate use to You may also use our online abuse reporting from: http://www.newsfeeds.com/abuseform.htm X-Abuse-Info: Please be sure to forward a copy of ALL headers, INCLUDING the body (DO NOT SEND ATTACHMENTS) Organization: Newsfeeds.com http://www.newsfeeds.com 100,000+ UNCENSORED Newsgroups. Path: news.easynews.com!newsfeed1.easynews.com!easynews.com!easynews!news.he.net!cyclone-sf.pbi.net!209.81.14.120!feeder.via.net!news-out.newsfeeds.com!l2!corp-news.newsgroups.com!not-for-mail Xref: newsfeed1.easynews.com comp.databases.theory:22904 X-Received-Date: Wed, 09 Oct 2002 19:36:26 MST (news.easynews.com) 71062.1056@compuserve.com (--CELKO--) wrote in news:c0d87ec0.0210091535.5ad009a6@posting.google.com: >>> I prefer the singular. :) Especially when it comes to cases >>> where > we have a table with one row. << > > Then use a singlular name and enforce this rule with a constraint: Thanks but no thanks. :) Seriously, if I may direct your attention to An Introduction to Database Systems (3rd edition - yeah I'm that old! :), page 88 Date uses his [supplier]/[part] example. Not [suppliers], [supplier] On page 85, he talks about [part], not [parts]. Take it up with him! >>> Because the business world is fickle, that one row table may >>> become > a multiple row table in the future. << > > Unh? Tables usually start as a set, not as a singularity. Not necessarily always. For instance, there might be a table called [site_config] that has a single row. >>> Additionally, when I validate the model, we talk about a single >>> row > against the cardinality: > > [master] ----> [detail] > (0,N) (1,1) > > A [master] may or may not have a [detail] > A [detail] has at least one and at most one [master] << > > You do know you are using terms from IMS and not SQL, don't you? > We have referenced and referencing columns, not "master" and > "details". I suppose I wasn't clear enough in my example, I was referring to the "relationship" between the [master] and the [detail] >>> You could also consider dashes instead of underscore. << > > That does not work in SQL, only in Cobol. I am using Google to > read this message and I have not scrolled down yet but I just know > you will say "parent" and "child" before the end of this post. You've munged the citing, I didn't write that. Use a real news reader! heh heh! >>> I don't get too worked up about case. << > > Human beings read lowercase MUCH more accurately than all > uppercase. When we had Cobol and used punch cards, it was a real > problem. This is why newspaper and book don't use all uppercase. > There are a ton of studies from the 1970's about readabilty of > code. Again, I didn't write the above either... I'm clipping all the parts of your response that I didn't write ... :) fyi: oracle uppercases all column names and Sybase ASE respects case. I personally prefer all lowercase for column names and reserved words in uppercase. >>> Using the above naming convention, where all tables get a >>> surrogate > key (builds a proper abstraction between implementation and the > business side changing its definition of a primary key!) and the > children tables building the FK as: parent + "primary column > name", developers can _easily_ code up their SQL. << > > It is an improper abstraction, based on PHYSICALLY and not LOGICAL > rules. That's correct that it's based on physical rules. Is it improper? I disagree completely. The important thing about the abstraction is that it considers the entire business problem at hand. I'm in the business of delivering high-performing and _maintainable_ systems. We need to ensure that the application isn't a stove-pipe application. We are trying to avoid that anti-pattern. With that, we build the abstraction so that the business can change quickly and the application can react quickly as well. > Primary keys do not change often or quickly -- see the > move from UPC to GTIN codes in retail. The fact is that primary keys _do_ change and the amount of re-work involved to fix the damage can be significant and extremely costly. I submit that building the abstraction with surrogate keys and using the business 'primary keys' as natural keys has a lot of gain with no loss of representation. > And I was right!! You used > "child and parent" right out of IMS and network databases!! Ugh, nooooooooooooo.... just the names I happened to pick. As you well know, a relational database can represent a network and/or hierarchical database therefore it's not surprising to see those names used. Well, not to me at least. > When I taught college, I could tell the language group of the > students by looking at their code and their first programming > language. Sorry, you missed the boat. I never wrote anything in IMS. >>> Additionally, if one is building triggers, it's very easy to >>> write > an EMACS keyboard macro to write the trigger code. I did this for > a client and built RI for 20 tables in 15 minutes. << > > Now your age is showing! EMACS is going to scare the kids reading > this I suppose so... Any Unix person worth their salt knows emacs... no? :) > Why didn't you use DRI actions instead of triggers? > Triggers are not portable yet in spite of Standards. They are > non-relational kludges and usually slow as hell. Please provide me with factual data that triggers, let's pick Sybase ASE, are 'slow as hell.' I don't think you can, I think you're wrong. Provide me the code and the benchmark that you use to prove your assertion. Also, let's define slow as hell: 30% slower? 50%? Thx! -- Pablo Sanchez, High-Performance Database Engineering http://www.hpdbe.com