Home -> Usenet -> comp.databases.oracle -> Re: Database Standards for Oracle Database design

Re: Database Standards for Oracle Database design

From: Oracle FAQ <orafaq_at_bf.rmit.edu.au>
Date: 1995/09/13
Message-ID: <436288$l9a_at_aggedor.rmit.EDU.AU> 







ganeshk_at_aol.com (GaneshK) writes:


>Can anyone suggest some standards to be used in Database design. We are a
>shop adopting Oracle7 and want to set standards upfront so that the
>projects adhere to them right from the beginning. The standards can
>include procedures and as well as naming conventions.





Here is the forthcoming page I'm working on for the FAQ.  You'll need
to get a Web browser to view it.  It's still raw, is not quite 'standards'
but nevermind.  Its a start.



No discussion entered into.  It's too religious an area to bother.
Regards


David



<!doctype HTML public "-//W3O//DTD W3 HTML 2.0//EN">

<HTML>

<HEAD>

<TITLE>Oracle FAQ : DB Design Guide</TITLE>

<META NAME="AUTHOR" VALUE="David T. Bath">

<LINK REV="made" HREF="mailto:orafaq_at_bf.rmit.edu.au">

<ISINDEX>


</HEAD>


 


<BODY>


<A NAME="TopOfFile">




        <H1>Oracle FAQ : DB Design Guide</H1>


</A>

<BR>

<P> <A HREF="contents.html">Back to Oracle FAQ contents page</A> </P>

<BR>

<A NAME="Summary"><H2>File Summary</H2></A>



	<H3>Subject</H3>
		<P>
		A quick overview of my own opinions (tested in the
		real world) on database design methods.	
		</P><P>
		This will probably offend many droids/suits.  I hope so.
		</P><P>
		Apart from MilSpec documents, almost every QA methodology
		seems to have been written by a commitee of suits, and
		merely ensures that you can fall flat on your face in
		a consistent manner, having nothing to do with 
		developing code that actually works.
		</P>
	<H3>Intended Audience</H3>
		<P>
		People who are sceptical about all the various
		methodologies being touted.  Be sceptical about these
		notes as well.
		</P>	
	<H3>Revision Summary</H3>
		<P>
		$Date$<BR>
		$Revision$<BR>
		$State$<BR>
		<A HREF="#RevisionLog">Revision log for this file</A>
		</P>
	<BR>
	<H2>Contents of this Page</H2>
	<MENU>
		<LI><A HREF="#BusModel">Business model first</A></LI>
		<LI><A HREF="#IdDom">Identify domains</A></LI>
		<LI><A HREF="#OutIn">Work from the outside in</A></LI>
		<LI><A HREF="#NumKey">Use numeric keys</A></LI>
	</MENU>
	<BR>
	</P>
	<P><A HREF="#TopOfFile">Back to top of this file</A></P>


<BR>

<BR>


<HR>

<BR>

<A NAME="BusModel">



	<H2>Business model first</H2></A>
	<P>
	Get the business model and requirements done first.  This should
	not involve ANY data modelling.
	</P> <P>
	Here is the difficult part: getting the business types to
	agree on what they want, and stick to it.  Given that most of
	the droids who want new systems have a cargo-cult mentality to
	software, believing that somehow software (and analysts) are
	psychic and will magically do what they want, they have no
	idea how much effort it takes to develop an accurate
	business model/spec - and sure as hell they don't want to
	pay for it.  (At least not now.  They much prefer the expense
	to show up as bug reports and fixes - it looks like you
	caused the problem not them.  But the company will pay in
	the long run.)
	</P> <P>
	People forget the basic rule of computers: <EM>GIGO</EM>.  Garbage
	in, garbage out.  And most business requirements reflect this
	amnesia.  Just because a spec weighs in at 50 kilgrams does not
	mean it is any good.
	</P> <P>
	Most systems fail (or simply never go into production) simply
	because of a poor business specification.
	</P> <P>
	A recent NASA study into the Voyager and Galileo projects showed
	that of the 197 critical errors, only three were programmer
	errors, the rest due to incorrect or unclear specifications.
	A study by JPL showed that two thirds of errors were due to
	incorrect or unclear requirements.
	</P> <P>
	By keeping data modelling out of the issue, you simplify the
	business specification process, and also prevent shackling your
	data-modellers with decisions made too early.
	</P>
	<P><A HREF="#TopOfFile">Back to top of this file</A></P>




<BR> <BR> <HR> <BR>


<A NAME="IdDom">



	<H2>Identify common domains</H2></A>
	<P>
	By this I mean to look around for columns or groups of
	columns that will be commonly used around your site.  Surnames,
	for example, should all be the same length.  Addresses are
	an example of a group of columns that should be consistent,
	and with a set of packaged functions developed to deal with
	them.
	</P> <P>
	Personal names are another common domain made up of a group
	of columns.  Consider putting in a flag for the convention
	used when putting surnames together: Asian (surname first)
	or European (surname last) and having a packaged function
	to assemble the name for letters, et al.  (How would you react
	to a letter starting 'Dear Mr Smith John, ....' ?)
	</P> <P>
	This sort of thing is damn difficult with a lot of systems
	now totally developed within departments.  No department is
	prepared to fork out the dollars to develop something of
	benefit to the whole company.  Corporate IT services need
	to be firm on this.
	</P> <P>
	Anyway, these columns and associated functions (sound a
	bit like objects, don't they?) should be tested, documented,
	handed to a librarian, and the information on them propagated
	to all developers so they don't go around re-inventing the
	wheel.  (And most wheels developed by applications developers
	follow the biblical prescription of having a circumference
	exactly three times the diameter.)
	</P> <P>
	(This is exactly the sort of work I love doing - 
	<A HREF="over2you.html#HelpSponsors">hire us</A> to help you).
	</P>
	<P><A HREF="#TopOfFile">Back to top of this file</A></P>




<BR> <BR> <HR> <BR>


<A NAME="OutIn">



	<H2>Work from the outside in</H2></A>
	<P>
	Start your data design from the outside of your still-nebulous
	data model.
	</P> <P>
	You can tell which entities are on the outside: they do not
	depend on anything else.  Many are code/description tables.
	</P> <P>
	Because these entities are simple, they are easy to implement.
	You can then quickly develop prototype screens/reports for these
	entities and populate them with real data.
	</P> <P>
	These early screens, having simple logic, let you sort out the
	look and feel of your application, and get approval from the
	users.  They are also ready for plugging in to the next layer
	of screens dealing with the more complex objects.  They are
	also simpler to train the early users with, and you can let
	them put the real data in for you, giving them something to
	do, and making it look like you are achieving something (which
	you are).
	</P> <P>
	Populating them with real data gives you a better chance of
	picking up problems with the next-most-complex layer of
	entities, be these design or implementation bugs.
	</P> <P>
	Each step inwards is resting on a solid base - the more you get
	towards the centre of your data model, the more complex things
	get, but the references are already in place.
	</P> <P>
	Oh, and if this is sounding like bottom-up programming, I make
	no apologies.  I consider it building on solid foundations
	rather, infinitely better than hanging walls from a roof and
	then putting the floor in, and then the foundations.
	</P>
	<P><A HREF="#TopOfFile">Back to top of this file</A></P>




<BR> <BR> <HR> <BR>


<A NAME="NumKey">



	<H2>Use numeric keys</H2></A>
	<P>
	Do not use strings as part of the primary keys.  These are
	likely to mutate, and besides, integers are quicker to compare
	when running down an index.  (Most compilers generate code
	that compares numbers a word at a time rather than byte-by-byte.)
	</P> <P>
	These integers should have no meaning and are probably generated
	using sequences.
	</P> <P>
	Of course, you can hide these numbers from users, giving them
	pseudo-keys to play with.
	</P> <P>
	If you think I'm wrong, consider the way Oracle stores the
	information in its internal tables such as SYS.USER$, SYS.OBJ$
	and SYS.COL$.  Numeric keys.  To find a column using the ninety
	bytes potentially required for schema, table and column name
	is grossly inefficient.
	</P>
	<P><A HREF="#TopOfFile">Back to top of this file</A></P>




<BR> <BR> <HR> <BR>

<A NAME="RevisionLog"><H2>Revision log for this file</H2></A>


<PRE>



$Log$


</PRE>

<BR>

<P><A HREF="#TopOfFile">Back to top of this file</A></P>

<BR>

<HR>

</BODY>


</HTML>


 


--
Oracle FAQ and archives maintainer
orafaq_at_bf.rmit.edu.au                 (private mail to dtb_at_bf.rmit.edu.au)
http://www.bf.rmit.edu.au/OracleFAQ   ftp://ftp.bf.rmit.edu.au/pub/Oracle/
http://www.bf.rmit.edu.au/Oracle      http://www.bf.rmit.edu.au/~orafaq
-- NOT TO BE ARCHIVED IN ANY MANNER ON MICROSOFT NETWORK --


Received on Wed Sep 13 1995 - 00:00:00 CEST












Original text of this message