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
