On Tue, May 3, 2011 at 8:03 PM, Greg Smith wrote:
> With a uniqueness constraint in this situation, the unexpected data--row
> with a non unique MAC--will be rejected and possibly lost when the insertion
> happens. You say that's a good thing, plenty of people will say that's the
> worst possibl
> Being the “first line” or the “second line” of a physical invoice is a
> property for that line. Identifying its position on the invoice is only
> natural.
> Specifically, the position of the line on the invoice; you can't have to
> invoice lines at the second line of aninvoice for example.
On Wed, May 4, 2011 at 2:25 AM, Greg Smith wrote:
> David Johnston wrote:
>>
>> Is there any rules-of-thumb on the performance of a PK as a function of
>> key length? I like using varchar based identifiers since I tend to query
>> tables directly and writing where clauses is much easier if you ca
On Wed, May 4, 2011 at 7:50 AM, Misa Simic wrote:
> 2011/5/4 Merlin Moncure
>>
>> Most of the old school accounting systems maintained an invoice line
>> number.
>> > Invoice Line
>> > -Invoice Number
>> > -LineNo
>> > -ItemID
>> > -qty
>> > -Price
>>
>> The line number starte
On Wed, May 04, 2011 at 09:33:57AM -0400, David Johnston wrote:
> “Hello - person born in Liverpool London, St. Whatever
> hospital, Room 101 @ 13:14:57AM on the 5th of March 2001 –
> how may I direct your call?” (I guess you could use the
> conception date as well
That will rarely be known to an
>>Thanks, merlin,
>>>And in that case, what is "Natural" in LineNo? I would say, with adding
>>>LineNo we are creating syntethic/surrogate Key (just instead of 1 surrogate
>>>column - it will be Compound key with more columns...)? The >>>same is with
>>>all other tables what are "parts" o
2011/5/4 Merlin Moncure
> Most of the old school accounting systems maintained an invoice line
> number.
>
> > Invoice Line
> > -Invoice Number
> > -LineNo
> > -ItemID
> > -qty
> > -Price
>
> The line number started from 1 (the first line on the invoice) on
> every unique invo
On Wed, May 4, 2011 at 7:14 AM, Misa Simic wrote:
>
>
> 2011/4/28 Merlin Moncure
>>
>> On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer wrote:
>> *) most tables don't have unique natural keys (let's see em)
>> etc
>>
>
> i.e for an Invoice, we have at least 2 tables (more in practice...):
> Invoice H
2011/4/28 Merlin Moncure
> On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer wrote:
> *) most tables don't have unique natural keys (let's see em)
> etc
>
>
i.e for an Invoice, we have at least 2 tables (more in practice...):
Invoice Header
-Invoice Number
-Date
-CustomerID
-Currency
David Johnston wrote:
Is there any rules-of-thumb on the performance of a PK as a function of key
length? I like using varchar based identifiers since I tend to query tables
directly and writing where clauses is much easier if you can avoid the joins.
I'm likely better off creating views and
On May 3, 2011, at 22:03, Greg Smith wrote:
> Merlin Moncure wrote:
>> If your data modeler that made the the assumptions that a MAC is
>> unique (a mistake obviously) at least the other tables are protected
>> from violations of that assumption because the database would reject
>> them with an
Jeff Davis wrote:
On Mon, 2011-05-02 at 23:07 -0400, Greg Smith wrote:
I see this whole area as being similar to SQL injection. The same way
that you just can't trust data input by the user to ever be secure, you
can't trust inputs to your database will ever be unique in the way you
expect
Merlin Moncure wrote:
If your data modeler that made the the assumptions that a MAC is
unique (a mistake obviously) at least the other tables are protected
from violations of that assumption because the database would reject
them with an error, which is a Good Thing. Without a uniqueness
constra
On 05/03/2011 03:08 PM, Jeff Davis wrote:
On Tue, 2011-05-03 at 13:35 -0600, Rob Sargent wrote:
Sorry, but I'm confused, but that's common. Isn't a "natural key" to be
compose solely from the attributes of the entity? As in a subset of the
columns of the table in a third-normalish world. Isn
On Mon, 2011-05-02 at 23:07 -0400, Greg Smith wrote:
> I see this whole area as being similar to SQL injection. The same way
> that you just can't trust data input by the user to ever be secure, you
> can't trust inputs to your database will ever be unique in the way you
> expect them to be.
On Tue, 2011-05-03 at 13:35 -0600, Rob Sargent wrote:
> Sorry, but I'm confused, but that's common. Isn't a "natural key" to be
> compose solely from the attributes of the entity? As in a subset of the
> columns of the table in a third-normalish world. Isn't tacking on
> another column with a
On 05/03/2011 12:51 PM, Jeff Davis wrote:
On Mon, 2011-05-02 at 20:06 -0600, Rob Sargent wrote:
Jeff Davis wrote:
In particular, I think you are falsely assuming that a natural key must
be generated from an outside source (or some source outside of your
control), and is therefore not reliably
On Mon, 2011-05-02 at 20:06 -0600, Rob Sargent wrote:
> Jeff Davis wrote:
> > In particular, I think you are falsely assuming that a natural key must
> > be generated from an outside source (or some source outside of your
> > control), and is therefore not reliably unique.
> >
> > You can generate
On Mon, May 2, 2011 at 11:53 PM, Craig Ringer
wrote:
> On 03/05/11 11:07, Greg Smith wrote:
>
>> That doesn't mean you can't use
>> them as a sort of foreign key indexing the data; it just means you can't
>> make them the sole unique identifier for a particular entity, where that
>> entity is a pe
On May 2, 2011, at 10:52 PM, Craig Ringer wrote:
> SSN? What if they don't live in the US or aren't a citizen?
Non-citizens can have SSNs (they have to if they work in the US).
--
Rick Genter
rick.gen...@gmail.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make c
On Tue, May 03, 2011 at 10:52:23AM +0800, Craig Ringer wrote:
> ... and that's before we get into the horror of "what is someone's
> name". Which name? Which spelling? Do they even have a single canonical
> name?
- people have, at least over time, several compound names
- they have, at any one ti
John R Pierce wrote:
otoh, there's plenty of places where natural keys are optimal. my
company makes widgets, and we make damn sure our serial #s and part
numbers are unique, and we use them as PK's for the various tables.
Sure; what I was commenting on is that you normally can't ever trust
On May 2, 2011, at 10:53 PM, Rob Sargent wrote:
> ...and you're at risk of having to reformat them when you buy out your
> competitor.
The scheme described was awfully similar to one that a client of mine used,
product family prefix, identifiers within the family. And guess what? The
scheme, w
On 03/05/11 12:57, Rob Sargent wrote:
> Hm.. Virtual machines as assets. Mortgage backed securities, anyone.
Well, sure ... but the software running on them is tracked as part of
licensing compliance efforts, whether or not the virtual hardware its
self is an "asset" its self. The DB designer ch
Craig Ringer wrote:
On 03/05/11 11:07, Greg Smith wrote:
That doesn't mean you can't use
them as a sort of foreign key indexing the data; it just means you can't
make them the sole unique identifier for a particular entity, where that
entity is a person, company, or part.
Classic ca
John R Pierce wrote:
otoh, there's plenty of places where natural keys are optimal. my
company makes widgets, and we make damn sure our serial #s and part
numbers are unique, and we use them as PK's for the various tables.
further, the PN has a N digit prefix which is unique to a part fam
On 03/05/11 11:07, Greg Smith wrote:
> That doesn't mean you can't use
> them as a sort of foreign key indexing the data; it just means you can't
> make them the sole unique identifier for a particular entity, where that
> entity is a person, company, or part.
Classic case: a database here has se
otoh, there's plenty of places where natural keys are optimal. my
company makes widgets, and we make damn sure our serial #s and part
numbers are unique, and we use them as PK's for the various tables.
further, the PN has a N digit prefix which is unique to a part family,
then a M digit suf
On 05/02/2011 10:06 PM, Rob Sargent wrote:
You would be surprise how many "bob smith"s where born on the same
day. But then they weren't all born in a hospital etc etc etc.
I wouldn't be surprised. I once lived in a mile-square town (Hoboken,
that's it's nickname). In that town were 40K res
On 03/05/11 10:06, Rob Sargent wrote:
> My wife works (at the sql level) with shall we say "records about
> people". Real records, real people. Somewhere around 2 million unique
> individuals, several million source records. They don't all have ssn,
> they don't all have a drivers license. The
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Rob Sargent
> Sent: Monday, May 02, 2011 7:07 PM
> To: Jeff Davis
> Cc: Greg Smith; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] perva
Jeff Davis wrote:
On Mon, 2011-05-02 at 11:10 -0400, Greg Smith wrote:
The position Merlin
has advocated here, that there should always be a natural key available
if you know the data well enough, may be true. But few people are good
enough designers to be sure they've made the decision cor
On Mon, May 2, 2011 at 7:43 PM, Craig Ringer
wrote:
> I'm now strongly in favour of keeping an internal key that users never
> see, and having separate user-visible identifiers. The users can demand
> that those identifiers change format or generation method and it's an
It's far easier to change
On 03/05/11 08:25, Jeff Davis wrote:
> You can generate your own keys, and if you hand them out to customers
> and include them on paperwork, they are now a part of the reality that
> your database models -- and therefore become natural keys. Invoice
> numbers, driver's license numbers, etc., are
On Mon, 2011-05-02 at 11:10 -0400, Greg Smith wrote:
> The position Merlin
> has advocated here, that there should always be a natural key available
> if you know the data well enough, may be true. But few people are good
> enough designers to be sure they've made the decision correctly, and th
On Mon, May 2, 2011 at 10:10 AM, Greg Smith wrote:
> On 05/01/2011 06:12 PM, Karsten Hilbert wrote:
>>
>> Good to know since I'm only a lowly medical doctor not
>> having much schooling in database matters beyond this list,
>> the PostgreSQL docs, and the Celko book.
>>
>
> This debate exists at a
I've been carefully reading all of the comments with great interest.
Thanks very much for the thoughtful responses - very enlightening.
- Jim (the topic originator)
Jim Irrer ir...@umich.edu (734) 647-4409
University of Michigan Hospital Radiation Oncology
519 W. William St.
On 05/01/2011 06:12 PM, Karsten Hilbert wrote:
Good to know since I'm only a lowly medical doctor not
having much schooling in database matters beyond this list,
the PostgreSQL docs, and the Celko book.
This debate exists at all levels of experience, and the only thing that
changes as you
On Thursday, April 28, 2011 08:31:09 PM Scott Ribe wrote:
> Well, natural keys are quite obviously the way to go, when they exist. The
> problem is, they usually don't really exist. What's usually proposed as a
> natural key, will upon further investigation, either not be guaranteed
> unique, or no
On Thu, Apr 28, 2011 at 3:07 PM, Karsten Hilbert
wrote:
> I must agree with a recent poster
> that what appears to identify as a natural key often really
> isn't or else becomes not so later on. It's vastly easier to
> then deal with that by re-defining constraints without
> having to touch prima
On Fri, Apr 29, 2011 at 10:14:07AM -0500, Merlin Moncure wrote:
> I took a quick look at the gnumed schema and found it to be generally
> very thorough and excellent. If you're going to use surrogate keys,
> that's they way to do it.
Good to know since I'm only a lowly medical doctor not
having
On Thu, Apr 28, 2011 at 4:07 PM, Karsten Hilbert
wrote:
> On Thu, Apr 28, 2011 at 03:39:19PM -0500, Merlin Moncure wrote:
>
>> They are fairly pervasive, and increasingly so, which I find to be
>> really unfortunate. Personally I think rote use of surrogate keys is
>> terrible and leads to bad ta
On Thu, Apr 28, 2011 at 03:39:19PM -0500, Merlin Moncure wrote:
> They are fairly pervasive, and increasingly so, which I find to be
> really unfortunate. Personally I think rote use of surrogate keys is
> terrible and leads to bad table designs, especially if you don't
> identify the true natura
l
response.
David J.
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andy Colson
Sent: Thursday, April 28, 2011 1:44 PM
To: Jim Irrer
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pervasiveness of surrogate (a
On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer wrote:
> A colleague of mine insists that using surrogate keys is the
> common practice by an overwhelming margin in relational databases and
> that they are used in 99 percent of large installations. I agree that many
> situations benefit from them, bu
On Thu, Apr 28, 2011 at 7:26 PM, Joshua D. Drake wrote:
> Well there is no fact to back that up but, I will say that most toolkits
> require the use of a synthetic key, rails, django etc
Usually such tools are born with surrogate keys only, because it's
easier, and either grow up developing
On Apr 28, 2011, at 11:53 AM, Rob Sargent wrote:
> Hm, I get the feeling that only the good folks at Hibernate seem to think
> using a "natural key" is the _only_ way to go.
Well, natural keys are quite obviously the way to go, when they exist. The
problem is, they usually don't really exist. W
On 04/28/2011 10:29 AM, Jim Irrer wrote:
A colleague of mine insists that using surrogate keys is the
common practice by an overwhelming margin in relational databases and
that they are used in 99 percent of large installations. I agree that
many
situations benefit from them, but are they real
On Thu, Apr 28, 2011 at 01:29:31PM -0400, Jim Irrer wrote:
> common practice by an overwhelming margin in relational databases and
> that they are used in 99 percent of large installations.
94.68536% of all the claims I ever hear are obviously pulled out of
thin air.
What conclusion does your
On 04/28/2011 11:44 AM, Andy Colson wrote:
On 4/28/2011 12:29 PM, Jim Irrer wrote:
A colleague of mine insists that using surrogate keys is the
common practice by an overwhelming margin in relational databases and
that they are used in 99 percent of large installations. I agree that
many
situa
On 4/28/2011 12:29 PM, Jim Irrer wrote:
A colleague of mine insists that using surrogate keys is the
common practice by an overwhelming margin in relational databases and
that they are used in 99 percent of large installations. I agree that many
situations benefit from them, but are they really
A colleague of mine insists that using surrogate keys is the
common practice by an overwhelming margin in relational databases and
that they are used in 99 percent of large installations. I agree that many
situations benefit from them, but are they really as pervasive
as he claims?
Thanks,
- Jim
52 matches
Mail list logo
