pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedAdvice on key design
10+ messages / 5 participants
[nested] [flat]
* Advice on key design
@ 2013-07-23 21:44 JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: JORGE MALDONADO @ 2013-07-23 21:44 UTC (permalink / raw)
To: pgsql-sql
I have 2 tables, a parent (tbl_persons) and a child
(tbl_languages_per_person) as follows (a language table is also involved):
------------------
tbl_persons
------------------
* per_id
* per_name
* per_address
--------------------------------------
tbl_languages_per_person
--------------------------------------
* lpp_person_id
* lpp_language_id
* lpp_id
As you can see, there is an obvious key in the child table which is
"lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a
unique key which is a field that contains a consecutive number of type
serial.
My question is: what should I configure as the primary key, "lpp_person_id
+ lpp_language_id" or "lpp_id"?
Is the role of a primary key different from that of a unique index?
With respect,
Jorge Maldonado
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Advice on key design
@ 2013-07-23 22:02 Anton Gavazuk <antongavazuk@gmail.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Anton Gavazuk @ 2013-07-23 22:02 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
Hi Jorge,
In your case it would be lpp_id as PK, and
lpp_person_id,lpp_language_id as unique constraint
Thanks,
Anton
On Jul 23, 2013, at 23:45, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> I have 2 tables, a parent (tbl_persons) and a child (tbl_languages_per_person) as follows (a language table is also involved):
>
> ------------------
> tbl_persons
> ------------------
> * per_id
> * per_name
> * per_address
>
> --------------------------------------
> tbl_languages_per_person
> --------------------------------------
> * lpp_person_id
> * lpp_language_id
> * lpp_id
>
> As you can see, there is an obvious key in the child table which is "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a unique key which is a field that contains a consecutive number of type serial.
>
> My question is: what should I configure as the primary key, "lpp_person_id + lpp_language_id" or "lpp_id"?
> Is the role of a primary key different from that of a unique index?
>
> With respect,
> Jorge Maldonado
>
>
>
>
>
>
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Advice on key design
@ 2013-07-23 22:28 JORGE MALDONADO <jorgemal1960@gmail.com>
parent: Anton Gavazuk <antongavazuk@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: JORGE MALDONADO @ 2013-07-23 22:28 UTC (permalink / raw)
To: Anton Gavazuk <antongavazuk@gmail.com>; +Cc: pgsql-sql
>> In your case it would be lpp_id as PK, and
>> lpp_person_id,lpp_language_id as unique constraint
>>
>> Thanks,
>> Anton
Is there a reason to do it the way you suggest?
Regards,
Jorge Maldonado
On Tue, Jul 23, 2013 at 5:02 PM, Anton Gavazuk <antongavazuk@gmail.com>wrote:
> Hi Jorge,
>
> In your case it would be lpp_id as PK, and
> lpp_person_id,lpp_language_id as unique constraint
>
> Thanks,
> Anton
>
> On Jul 23, 2013, at 23:45, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
>
> > I have 2 tables, a parent (tbl_persons) and a child
> (tbl_languages_per_person) as follows (a language table is also involved):
> >
> > ------------------
> > tbl_persons
> > ------------------
> > * per_id
> > * per_name
> > * per_address
> >
> > --------------------------------------
> > tbl_languages_per_person
> > --------------------------------------
> > * lpp_person_id
> > * lpp_language_id
> > * lpp_id
> >
> > As you can see, there is an obvious key in the child table which is
> "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a
> unique key which is a field that contains a consecutive number of type
> serial.
> >
> > My question is: what should I configure as the primary key,
> "lpp_person_id + lpp_language_id" or "lpp_id"?
> > Is the role of a primary key different from that of a unique index?
> >
> > With respect,
> > Jorge Maldonado
> >
> >
> >
> >
> >
> >
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Advice on key design
@ 2013-07-24 08:16 Anton Gavazuk <antongavazuk@gmail.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Anton Gavazuk @ 2013-07-24 08:16 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
The reason is simple - as you need the artificial PK lpp_id, then
everything else becomes an constraint
Thanks,
Anton
On Jul 24, 2013, at 0:28, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
>> In your case it would be lpp_id as PK, and
>> lpp_person_id,lpp_language_id as unique constraint
>>
>> Thanks,
>> Anton
Is there a reason to do it the way you suggest?
Regards,
Jorge Maldonado
On Tue, Jul 23, 2013 at 5:02 PM, Anton Gavazuk <antongavazuk@gmail.com>wrote:
> Hi Jorge,
>
> In your case it would be lpp_id as PK, and
> lpp_person_id,lpp_language_id as unique constraint
>
> Thanks,
> Anton
>
> On Jul 23, 2013, at 23:45, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
>
> > I have 2 tables, a parent (tbl_persons) and a child
> (tbl_languages_per_person) as follows (a language table is also involved):
> >
> > ------------------
> > tbl_persons
> > ------------------
> > * per_id
> > * per_name
> > * per_address
> >
> > --------------------------------------
> > tbl_languages_per_person
> > --------------------------------------
> > * lpp_person_id
> > * lpp_language_id
> > * lpp_id
> >
> > As you can see, there is an obvious key in the child table which is
> "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a
> unique key which is a field that contains a consecutive number of type
> serial.
> >
> > My question is: what should I configure as the primary key,
> "lpp_person_id + lpp_language_id" or "lpp_id"?
> > Is the role of a primary key different from that of a unique index?
> >
> > With respect,
> > Jorge Maldonado
> >
> >
> >
> >
> >
> >
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Advice on key design
@ 2013-07-24 08:23 Stanley Hui <ufrufr@gmail.com>
parent: Anton Gavazuk <antongavazuk@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Stanley Hui @ 2013-07-24 08:23 UTC (permalink / raw)
To: Anton Gavazuk <antongavazuk@gmail.com>; +Cc: JORGE MALDONADO <jorgemal1960@gmail.com>; pgsql-sql
Agreed with Anton, as PK, lpp_id is easier to be managed than (lpp_person_id
+ lpp_language_id)
One more suggestion, foreign key constraints could be setup on lpp_person_id
and lpp_language_id to link with target tables.
Thanks,
Stan
2013/7/24 Anton Gavazuk <antongavazuk@gmail.com>
> The reason is simple - as you need the artificial PK lpp_id, then
> everything else becomes an constraint
>
> Thanks,
> Anton
>
> On Jul 24, 2013, at 0:28, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
>
> >> In your case it would be lpp_id as PK, and
> >> lpp_person_id,lpp_language_id as unique constraint
> >>
> >> Thanks,
> >> Anton
>
> Is there a reason to do it the way you suggest?
>
> Regards,
> Jorge Maldonado
>
>
> On Tue, Jul 23, 2013 at 5:02 PM, Anton Gavazuk <antongavazuk@gmail.com>wrote:
>
>> Hi Jorge,
>>
>> In your case it would be lpp_id as PK, and
>> lpp_person_id,lpp_language_id as unique constraint
>>
>> Thanks,
>> Anton
>>
>> On Jul 23, 2013, at 23:45, JORGE MALDONADO <jorgemal1960@gmail.com>
>> wrote:
>>
>> > I have 2 tables, a parent (tbl_persons) and a child
>> (tbl_languages_per_person) as follows (a language table is also involved):
>> >
>> > ------------------
>> > tbl_persons
>> > ------------------
>> > * per_id
>> > * per_name
>> > * per_address
>> >
>> > --------------------------------------
>> > tbl_languages_per_person
>> > --------------------------------------
>> > * lpp_person_id
>> > * lpp_language_id
>> > * lpp_id
>> >
>> > As you can see, there is an obvious key in the child table which is
>> "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a
>> unique key which is a field that contains a consecutive number of type
>> serial.
>> >
>> > My question is: what should I configure as the primary key,
>> "lpp_person_id + lpp_language_id" or "lpp_id"?
>> > Is the role of a primary key different from that of a unique index?
>> >
>> > With respect,
>> > Jorge Maldonado
>> >
>> >
>> >
>> >
>> >
>> >
>>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Advice on key design
@ 2013-07-24 08:38 Bèrto ëd Sèra <berto.d.sera@gmail.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Bèrto ëd Sèra @ 2013-07-24 08:38 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
Hi,
> I also need the field "lpp_id" as a unique key which is a field that
contains a consecutive number of type serial.
Are you sure you REALLY need this? It looks like a lot of trouble for
nothing, as you now have one thing warranting the unicity of the record
(your serial) plus a unique key warranting you do not couple twice the same
language with the same person.
Normally, you'd rather have something like
(person_id)
(language_id)
(weight)
where the primary key is (person_id, language_id) (hence a simple Cartesian
product, both elements of the key being FKs to Language and People, or
whatever you call your tables) and weight is outside the key, but used for
sorting, so you have a preferred language.
What would be the rationale behind the serial number?
Cheers
Bèrto
On 23 July 2013 22:44, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> I have 2 tables, a parent (tbl_persons) and a child
> (tbl_languages_per_person) as follows (a language table is also involved):
>
> ------------------
> tbl_persons
> ------------------
> * per_id
> * per_name
> * per_address
>
> --------------------------------------
> tbl_languages_per_person
> --------------------------------------
> * lpp_person_id
> * lpp_language_id
> * lpp_id
>
> As you can see, there is an obvious key in the child table which is
> "lpp_person_id + lpp_language_id", but I also need the field "lpp_id" as a
> unique key which is a field that contains a consecutive number of type
> serial.
>
> My question is: what should I configure as the primary key, "lpp_person_id
> + lpp_language_id" or "lpp_id"?
> Is the role of a primary key different from that of a unique index?
>
> With respect,
> Jorge Maldonado
>
>
>
>
>
>
>
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Advice on key design
@ 2013-07-24 09:39 Luca Ferrari <fluca1978@infinito.it>
parent: Bèrto ëd Sèra <berto.d.sera@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Luca Ferrari @ 2013-07-24 09:39 UTC (permalink / raw)
To: Bèrto ëd Sèra <berto.d.sera@gmail.com>; +Cc: JORGE MALDONADO <jorgemal1960@gmail.com>; pgsql-sql
On Wed, Jul 24, 2013 at 10:38 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:
> What would be the rationale behind the serial number?
>
The serial key, also named "surrogate key" is there for management
purposes. Imagine one day you find out your database design is wrong
and what was unique the day before is no more so, how can you find
your records?
The idea is to have a surrogate key to save you from real world
troubles, and then constraints to implement the database design.
I usually use this convention:
- primary surrogate keys named pk and defined as primary keys
- database design keys named _key and defined with a unique constraint.
Luca
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Advice on key design
@ 2013-07-24 09:47 Bèrto ëd Sèra <berto.d.sera@gmail.com>
parent: Luca Ferrari <fluca1978@infinito.it>
0 siblings, 1 reply; 10+ messages in thread
From: Bèrto ëd Sèra @ 2013-07-24 09:47 UTC (permalink / raw)
To: Luca Ferrari <fluca1978@infinito.it>; +Cc: JORGE MALDONADO <jorgemal1960@gmail.com>; pgsql-sql
Hi,
It looks heavy, performance-wise. If this is not OLTP intensive you can
probably survive, but I'd still really be interested to know ow you can end
up having non unique records on a Cartesian product, where the PK is
defined by crossing the two defining tables. Unless you take your PK down
there is no way that can happen, and even if it does, a cartesian product
defining how many languages a user speaks does not look like needing more
than killing doubles. So what would be the rationale for investing process
into this?
Get me right, just trying to understand what you guys are doing.
Bèrto
On 24 July 2013 10:39, Luca Ferrari <fluca1978@infinito.it> wrote:
> On Wed, Jul 24, 2013 at 10:38 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com>
> wrote:
>
> > What would be the rationale behind the serial number?
> >
>
> The serial key, also named "surrogate key" is there for management
> purposes. Imagine one day you find out your database design is wrong
> and what was unique the day before is no more so, how can you find
> your records?
> The idea is to have a surrogate key to save you from real world
> troubles, and then constraints to implement the database design.
>
> I usually use this convention:
> - primary surrogate keys named pk and defined as primary keys
> - database design keys named _key and defined with a unique constraint.
>
> Luca
>
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Advice on key design
@ 2013-07-24 10:05 Luca Ferrari <fluca1978@infinito.it>
parent: Bèrto ëd Sèra <berto.d.sera@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Luca Ferrari @ 2013-07-24 10:05 UTC (permalink / raw)
To: Bèrto ëd Sèra <berto.d.sera@gmail.com>; +Cc: JORGE MALDONADO <jorgemal1960@gmail.com>; pgsql-sql
On Wed, Jul 24, 2013 at 11:47 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:
> Hi,
>
> It looks heavy, performance-wise. If this is not OLTP intensive you can
> probably survive, but I'd still really be interested to know ow you can end
> up having non unique records on a Cartesian product, where the PK is defined
> by crossing the two defining tables. Unless you take your PK down there is
> no way that can happen, and even if it does, a cartesian product defining
> how many languages a user speaks does not look like needing more than
> killing doubles. So what would be the rationale for investing process into
> this?
You are probably right: you are like to never refactor this kind of
design, and this situation using a surrogate key is useless. But what
happens if your language is no more uniquely identified by
lpp_language_id? Suppose you need to track also the language version
and therefore a language is identified by the couple (id, version). In
this case you have to refactor two tables: the language one and the
person-language join table.
Having a surrogate key on both sides allows you to smoothly add such
constraint without having to refactor the latter table and ensuring
all previous joins still work.
Ok, not a really smart example, but the only one that comes into my
mind at the moment.
Luca
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Advice on key design
@ 2013-07-24 10:12 Bèrto ëd Sèra <berto.d.sera@gmail.com>
parent: Luca Ferrari <fluca1978@infinito.it>
0 siblings, 0 replies; 10+ messages in thread
From: Bèrto ëd Sèra @ 2013-07-24 10:12 UTC (permalink / raw)
To: Luca Ferrari <fluca1978@infinito.it>; +Cc: JORGE MALDONADO <jorgemal1960@gmail.com>; pgsql-sql
Hi,
yeah, I am okay with design prudence, just used to be so paranoid about
performance that just any possible "one more thing to do" gets me nervous
:) Language versions do exist, say Dutch has different orthography
depending on what convention is used, so you may well need to suddenly add
a further level of definition. But you can pretty much do that by a
sequence of alter tables, especially in a situation like this.
Oh well, we are discussing principles as applied to a practical situation
that we actually ignore, so...
It's been a pleasure, but unless we get more detail... not much we can do
apart from putting out personal preferences :)
Bèrto
On 24 July 2013 11:05, Luca Ferrari <fluca1978@infinito.it> wrote:
> On Wed, Jul 24, 2013 at 11:47 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com>
> wrote:
> > Hi,
> >
> > It looks heavy, performance-wise. If this is not OLTP intensive you can
> > probably survive, but I'd still really be interested to know ow you can
> end
> > up having non unique records on a Cartesian product, where the PK is
> defined
> > by crossing the two defining tables. Unless you take your PK down there
> is
> > no way that can happen, and even if it does, a cartesian product defining
> > how many languages a user speaks does not look like needing more than
> > killing doubles. So what would be the rationale for investing process
> into
> > this?
>
>
> You are probably right: you are like to never refactor this kind of
> design, and this situation using a surrogate key is useless. But what
> happens if your language is no more uniquely identified by
> lpp_language_id? Suppose you need to track also the language version
> and therefore a language is identified by the couple (id, version). In
> this case you have to refactor two tables: the language one and the
> person-language join table.
> Having a surrogate key on both sides allows you to smoothly add such
> constraint without having to refactor the latter table and ensuring
> all previous joins still work.
> Ok, not a really smart example, but the only one that comes into my
> mind at the moment.
>
> Luca
>
--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a
darkened room munching pills and listening to repetitive music.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2013-07-24 10:12 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-23 21:44 Advice on key design JORGE MALDONADO <jorgemal1960@gmail.com>
2013-07-23 22:02 ` Anton Gavazuk <antongavazuk@gmail.com>
2013-07-23 22:28 ` JORGE MALDONADO <jorgemal1960@gmail.com>
2013-07-24 08:16 ` Anton Gavazuk <antongavazuk@gmail.com>
2013-07-24 08:23 ` Stanley Hui <ufrufr@gmail.com>
2013-07-24 08:38 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2013-07-24 09:39 ` Luca Ferrari <fluca1978@infinito.it>
2013-07-24 09:47 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
2013-07-24 10:05 ` Luca Ferrari <fluca1978@infinito.it>
2013-07-24 10:12 ` Bèrto ëd Sèra <berto.d.sera@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox