Re: Database design question

[Raul Jager <raul@xxxxxxxxxx>]

A primary key is a good idea, but not really necessary. If you have a
field, or group of fields that are unique, and that are very unlikely to
change (like birth date) you can use as primary key.  DB2  handles very
well a primary key that has several fields, so you can use it.  Some
other databases will only work well with a single field as primary key,
and even will require correlative numbers.

On 08/28/2017 12:12 PM, Bob Cagle wrote:
> So, I'm a little late to the DDL party.  Since I work on JD Edwards, I seldom have the need to create a new file.  But, working on one now.
>
> I'm not wanting to start a religious argument here, but I'm afraid this is going to...
>
> So I've read up on DDL and came across some recommendations for best practices for database design.  These two points specifically are what I'm questioning:
>
> .         Every table should have a primary key. The Primary Key should be an identity column, NOT business data
> .         Create business key as unique from business data, not Primary Key
>
> I'm creating a simple Customer address file:  Last Name, First Name, Address, City, State, Zip.  So I've created an auto-incrementing identity field as id.
>
> In my mind, the id should also be unique, but the recommendation is to only use business data.  What's the point of having a Primary Key separate from a Unique key?  In the past, I would've created a 'Customer#' field and incremented that field in the RPG.  The Customer# field would be both unique and primary.  
>
> So to follow these best-practices, do I create the Customer# row in addition to id as identity and mark Customer# as Unique?  
>
> If so, then can I set Customer# to auto-increment without marking it as identity, or do I still handle the incrementing in the program?
>
> Or do I forgo the Customer# row entirely and define the Unique Key from the rest of the fields?  I would basically have to include every field in order to make it unique.
>
> I'm open to suggestions/discussion?
>
> Thanks
> Bob Cagle
> IT Manager
> Lynk


-- Este e-mail fue enviado desde el Mail Server del diario ABC Color -- 
            -- Verificado por Anti-Virus Corporativo Symantec --