Re: Database design question -- MIDRANGE-L

What I hear you saying is that you have a customer number that is a just an
integer number or bigint. In that case, I would just use the id column as
the customer number.

The surrogate key comes into play where say you have an Warehouse number
and a shelf or something so you define a surrogate key being the unique key.

Define the surrogate key as part of the table and if you are accessing the
table with file i/o, define a logical (index) view with the surrogate key.

I don't know if you are clear on the primary key. If a database is being
designed correctly, the primary key (The identity key) is what links two
tables together. Not the surrogate key.

So if you have an order header and a order line table the order header has
an identity key which is placed in each order line to link the order header
to the order line.

That is in the perfect world. Many times we cannot do that because of
legacy tables defined in the dark ages.

Hopefully this is clear.

On Mon, Aug 28, 2017 at 9:12 AM, Bob Cagle <bcagle@xxxxxxxxxxx> 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

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD