Also, lets assume for a moment that LineItemNumber had a business function. It determines the order in which the order lines display on the invoice.
Had I introduced that composite key column in my OrderPayments example I would have to change data, not just in my OrderLineItems table, but also in the OrderPayments table (and myriad of other potential tables) if I wanted to change the order in which the line items appear on the invoice. For instance, I want bundled items to show in a particular order.
You are now increasing the complexity of the line reordering process.
Had you used the single primary / surrogate key in the OrderPayments table you would have saved yourself lines of code in your program, and increased your data I/O because you have to touch fewer files.
-----Original Message-----
From: Matt Olson [mailto:Matt.Olson@xxxxxxxx]
Sent: Monday, August 28, 2017 1:27 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Database design question
It does, but the moment you introduce changes to the program like my OrderPayments example you ultimately reduce the number of columns in the long run.
Software changes frequently in most places, because business rules change and because feature requests come in.
Designing your database this way, makes you more agile / quick to respond to those changes.
-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Monday, August 28, 2017 1:25 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Database design question
As far as the space considerations adding a surrogate key, in addition to order number and line number, takes space also.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Matt Olson <Matt.Olson@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 08/28/2017 02:19 PM
Subject: RE: Database design question
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Order Detail would have 3 columns, OrderDetailId (surrogate Key),
OrderNumber, and maybe LineItemNumber.
Lets say you have a new feature of your order processing system, whereby
you associate payments to an order, but folks can partial pay for an
order. We create a new file called OrderPayments.
OrderPayments:
OrderPaymentId (integer surrogate key), OrderDetailId, Amount,
PaymentType, PaymentDate.
Had we needed to use composite keys in your example it would probably look
like this (OrderNumber and LineItemNumber must now be used to uniquely
identify a payment against a order line):
OrderPaymentId (integer surrogate key), OrderNumber, LineItemNumber,
Amount, PaymentType, PaymentDate.
Sure it's only one extra column in the OrderPayments file. And sure you
can put your big boy pants on and write the extra join statement in
potentially many programs.
But have you considered scalability? That extra column can cost you disk
space, memory, and data retrieval time. What happens when your orderliness
table contains 100 million rows? More disk space and Larger database
indexes.
Now, say we replicated that composite key column to not just one table,
but it was in hundreds of tables. You start increasing complexity of SQL
querys, decreasing mantainability, and thus increasing costs
(maintainability costs being one of those).
-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Monday, August 28, 2017 1:06 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Database design question
I was thinking more of the orderdetail table with a primary key of
ordernumber and orderline. To me that shouldn't need a surrogate key. I'm
a big boy, if I need to put multiple columns in a join, or an update
statement where or some such thing I can handle it.
27 columns seems rather intense though.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Matt Olson <Matt.Olson@xxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 08/28/2017 01:53 PM
Subject: RE: Database design question
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
Primary keys should be a single column. If you introduce more you create
what is called a composite key. They are bane of existence for ORM's and
pretty much anyone who needs to write SQL join statements.
Rather than doing a JOIN on with a single column to another table that
also contains that column, you then would have to include all those
composite keys in the other table as well, and you propogate redundant
data amongst tables. I'd rather just propogate one facet of data (the
single column key) rather than more than one column. But if you like
typing and redundant key data amongst multiple tables, composite keys are
the way to go 😊
We have one table that has no single primary key. In order for us to issue
a delete against that record we have to do a WHERE clause with 27 columns!
I am not kidding on this. I have wanted to change it for years over to DDL
and slap on a surrogate key at the front of it, but it requires a lot of
RPG programs to be recompiled and no one wants to do it.
-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Monday, August 28, 2017 12:32 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Database design question
Another thing to consider is: There is nothing which states that a
primary key has to be a single column. I'm thinking of the JDEdwards
example.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail
to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
--
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
midrange.com
