Article: Database Design for Applications

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Site Related Forums

Article Discussion

Article: Database Design for Applications

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-10-26 : 21:51:57

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

3246 Posts

Posted - 2003-10-28 : 02:45:13

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 [url]http://weblogs.sqlteam.com[/url]

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-10-28 : 07:25:46

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.

TurdSpatulaWarrior
Starting Member

36 Posts

Posted - 2003-10-29 : 14:35:55

I especially appreciated the part about object naming convention. Additionally, I hate working on systems that caps lock addicts have set up. Great article!

Lavos
Posting Yak Master

200 Posts

Posted - 2003-10-29 : 15:26:49

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

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-29 : 19:31:53

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

Tim
Starting Member

392 Posts

Posted - 2003-10-30 : 18:14:59

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

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-10-30 : 20:00:25

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.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-10-31 : 06:49:39

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'"

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-31 : 08:30:34

Better:
... ...
FROM "Data
INNER JOIN Codes ON Data.this = Code.that
WHEN phase_of_the_moon = 'gibbous'"

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-10-31 : 08:35:03

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.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-31 : 08:46:08

So, this condition can be safely removed?

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-10-31 : 08:55:19

You can just say WHERE 1
Ask Jeff

Damian

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-31 : 09:04:08

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.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-31 : 09:10:42

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-31 : 09:31:37

Seems Stoad's overworked and now he is going slightly mad...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources