SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Database Design for Applications
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 10/26/2003 :  21:51:57  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Over the years that I've worked with databases I've worked with many packaged applications. Some were commercial applications and some were open source applications. Typically I'm trying to put data in, get data out or just figure out where the data went. So I've compiled a small list of things I wish developers would do when they build database applications (and a rant of things I wish they wouldn't do). Some are little things and some are big things. All of them will make the life of the DBA that uses your software better. And it's ALL about the DBA!

Article Link.

AjarnMark
SQL Slashing Gunting Master

USA
3246 Posts

Posted - 10/28/2003 :  02:45:13  Show Profile  Visit AjarnMark's Homepage  Reply with Quote
Great article Graz! I particularly agree with the singular vs. plural table names. I don't accept the OO-like argument that a table is a collection of (for example) customers and a single row represents one customer. BAH! I've had way too many headaches caused by confusion in singular vs. plurals in table names and programming variables. Who needs it?!

And I'd like to add this: Mr. Application Vendor, don't even THINK about asking me to grant you or your installation package sysadmin privileges. You'll be damn lucky if I even consider giving you db_owner on a database dedicated to your application. Yes, believe it or not, I had a vendor ask me to provide his installation consultant with a sysadmin password so he could create the database they needed. This was a Web Content Management system, which for now shall remain nameless, that my client had purchased. I almost felt bad for laughing so loud when I got the request. I insisted that he give me the build scripts and I ran them manually after inspecting them closely. Of course there was nothing there that required sysadmin privileges, just enough permissions to create a new database and create objects. But the lazy approach that too many vendors take is to ask (some even insist) that they have to have sysadmin rights. Again, I say BAH!

--------------------------------------------------------
Visit the SQLTeam Weblogs at http://weblogs.sqlteam.com
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 10/28/2003 :  07:25:46  Show Profile  Visit graz's Homepage  Reply with Quote
That's a very good point! Probably the result of developing as SA rather than a database user or DBO. Have to keep that in mind if I write a part 2.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

TurdSpatulaWarrior
Starting Member

USA
36 Posts

Posted - 10/29/2003 :  14:35:55  Show Profile  Reply with Quote
I especially appreciated the part about object naming convention. Additionally, I hate working on systems that caps lock addicts have set up. Great article!
Go to Top of Page

Lavos
Posting Yak Master

USA
200 Posts

Posted - 10/29/2003 :  15:26:49  Show Profile  Reply with Quote
I'm ambivalent towards plural or singular, but tend to lean more towards the plural side. As long as it is consistant, then I don't really see a major problem. Is it a question of getting the wrong name for an object or something else?

That said, prefixing object names with tbl_foo, usp_foo, vw_foo, etc. etc. is probably one of the more persistant bad habits I've seen. Modern development tools have made the bastardized version of hungarian notation (used properly, I almost like HN, but things like HWND* hwnd make me puke.) that people have adopted completely irrelevant (unfortunately, Query Analyzer isn't up there with the latest tools. maybe in Yukon it will be. (Or whatever else replaces it.))

One use of prefixing came in my last project when each functional area prefixed their tables so we could identify where something came from.

As for developing as SA, why not? Don't you just love it when your application suddenly breaks because someone accidently dropped all the objects in the database? ;)


----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

"Where theres a will, theres a kludge." - Ken Henderson
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/29/2003 :  19:31:53  Show Profile  Visit Merkin's Homepage  Reply with Quote
Here is another one that is annoying me today...

Don't use reserved words for object names or column names!
I am looking at a database that has some table names like :

user
index
log
group

These are all reserved words and make life really difficult. SELECT * FROM index really makes things barf. Yes I can use square brackets around them, but I shouldn't have to do that just because someone didn't think about the consequences of the table names they chose.

Grrr


Damian
Go to Top of Page

Tim
Starting Member

Australia
392 Posts

Posted - 10/30/2003 :  18:14:59  Show Profile  Reply with Quote
graz, I am happy to report that by sheer co-incidence, my latest database complies with all "Graz Directives" - except that I used Plurals for table names (CELKO to blame for this).

So now when I engage you on consulting I should expect lower fee as "System Orientation" will not be required ?????
Go to Top of Page

graz
Chief SQLTeam Crack Dealer

USA
4137 Posts

Posted - 10/30/2003 :  20:00:25  Show Profile  Visit graz's Homepage  Reply with Quote
You just pay travel and I'll give you one heck of a great deal!

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/31/2003 :  06:49:39  Show Profile  Reply with Quote
Could be worse.


CREATE TABLE "Data
INNER JOIN Codes ON Data.this = Code.that
WHERE phase_of_the_moon = 'gibbous'" (
  a int
)

SELECT *
FROM "Data
INNER JOIN Codes ON Data.this = Code.that
WHERE phase_of_the_moon = 'gibbous'"

Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 10/31/2003 :  08:30:34  Show Profile  Visit Stoad's Homepage  Reply with Quote
Better:
... ...
FROM "Data
INNER JOIN Codes ON Data.this = Code.that
WHEN phase_of_the_moon = 'gibbous'"
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 10/31/2003 :  08:35:03  Show Profile  Reply with Quote
Disagree! The moon is always gibbous from somewhere: it just depends on where you're standing (or floating).
However, Query Analyzer still mistakes it for a keyword, which is the main thing.

Edited by - Arnold Fribble on 10/31/2003 08:36:33
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 10/31/2003 :  08:46:08  Show Profile  Visit Stoad's Homepage  Reply with Quote
So, this condition can be safely removed?
Go to Top of Page

Merkin
Funky Drop Bear Fearing SQL Dude!

Australia
4970 Posts

Posted - 10/31/2003 :  08:55:19  Show Profile  Visit Merkin's Homepage  Reply with Quote
You can just say WHERE 1
Ask Jeff



Damian
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 10/31/2003 :  09:04:08  Show Profile  Visit Stoad's Homepage  Reply with Quote
Indeed, why T-SQL is insensitive to the outside world time?
It should be corrected. Like this:

select * from t
where ... and when @@CurrentDay not in ('Saturday', 'Sunday')

PS I strongly prefer the plural form of table names
and totally agree with the rest of graz's observations.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 10/31/2003 :  09:10:42  Show Profile  Visit jsmith8858's Homepage  Reply with Quote


- Jeff
Go to Top of Page

Stoad
Freaky Yak Linguist

*
1983 Posts

Posted - 10/31/2003 :  09:31:37  Show Profile  Visit Stoad's Homepage  Reply with Quote
Seems Stoad's overworked and now he is going slightly mad...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000