Re: Table Design In General

Chris wrote:
> I am a programmer with extensive exposure working with Oracle
> databases. However, up until now, I have been working with databases
> that were either designed without my input, or for other applications.
> This is my first forray into the actual design of the database.
>
> I have been doing some reading and am just starting to grasp the
> concept of using a "dumb" primary key in combination with a unique key.
> I understand the theory, but am not 100% sure on the practice.
>
> How does one go about working with tables that are linked via "dumb"
> keys? Is the best practice to create a View that gives you all the data
> you need to see, and edit it accordingly?
>
> As a newbie to this process, I appreciate any time you may take to
> respond. If you can point me to any online literature that would also
> be appreciated.
>
> Thanks in advance,
>
> Chris

The best advice is to not use surrogate keys if there are natural keys that define the record. This is not to say that surrogate keys are not necessary in some contexts but the SQL Server/MS Access type of attitude of autonumbering everything by default has nothing to do with best practice in an RDBMS. The secret is to know the difference between data that is a natural key and data that is "most of the time" a natural key.

That said there is no value in creating a view unless there is a business reason to do so. Just creating a view to denormalize should not be default behavior.

PS: I definitely recomment the books previously mentioned.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)