public inbox for [email protected]
help / color / mirror / Atom feedLookup tables
27+ messages / 9 participants
[nested] [flat]
* Lookup tables
@ 2025-02-04 14:27 Rich Shepard <[email protected]>
2025-02-04 14:41 ` Re: Lookup tables David G. Johnston <[email protected]>
2025-02-04 14:46 ` Re: Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables Ron Johnson <[email protected]>
2025-02-04 15:11 ` Re: Lookup tables Tony Shelver <[email protected]>
2025-02-04 16:31 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 19:08 ` Re: Lookup tables Rich Shepard <[email protected]>
0 siblings, 6 replies; 27+ messages in thread
From: Rich Shepard @ 2025-02-04 14:27 UTC (permalink / raw)
To: pgsql-general
Should lookup tables have a numeric FK column as well as the description column?
If so, how should I add an FK to the two lookup tables in my database?
TIA,
Rich
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 14:41 ` David G. Johnston <[email protected]>
2025-02-04 14:56 ` Re: Lookup tables Ron Johnson <[email protected]>
5 siblings, 1 reply; 27+ messages in thread
From: David G. Johnston @ 2025-02-04 14:41 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Tuesday, February 4, 2025, Rich Shepard <[email protected]> wrote:
> Should lookup tables have a numeric FK column as well as the description
> column?
>
> If so, how should I add an FK to the two lookup tables in my database?
>
Most do (have a surrogate PK) since it removes cascading updates and is a
smaller value.
Lots of alter tables and update queries.
David J.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:41 ` Re: Lookup tables David G. Johnston <[email protected]>
@ 2025-02-04 14:56 ` Ron Johnson <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables David G. Johnston <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Ron Johnson @ 2025-02-04 14:56 UTC (permalink / raw)
To: pgsql-general
On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston <[email protected]>
wrote:
> On Tuesday, February 4, 2025, Rich Shepard <[email protected]>
> wrote:
>
>> Should lookup tables have a numeric FK column as well as the description
>> column?
>>
>> If so, how should I add an FK to the two lookup tables in my database?
>>
>
> Most do (have a surrogate PK) since it removes cascading updates
>
How does a synthetic PK "remove cascading updates"? Doesn't the decision
on whether or not to cascade update depend on the ON UPDATE CASCADE clause
of the FK definition?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:41 ` Re: Lookup tables David G. Johnston <[email protected]>
2025-02-04 14:56 ` Re: Lookup tables Ron Johnson <[email protected]>
@ 2025-02-04 14:59 ` David G. Johnston <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: David G. Johnston @ 2025-02-04 14:59 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
On Tuesday, February 4, 2025, Ron Johnson <[email protected]> wrote:
> On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston <
> [email protected]> wrote:
>
>> On Tuesday, February 4, 2025, Rich Shepard <[email protected]>
>> wrote:
>>
>>> Should lookup tables have a numeric FK column as well as the description
>>> column?
>>>
>>> If so, how should I add an FK to the two lookup tables in my database?
>>>
>>
>> Most do (have a surrogate PK) since it removes cascading updates
>>
>
> How does a synthetic PK "remove cascading updates"? Doesn't the decision
> on whether or not to cascade update depend on the ON UPDATE CASCADE clause
> of the FK definition?
>
>
People don’t change synthetic PKs so updates never have to happen on the FK
side. Labels do get changed, in which case you have to update the FK label
side.
David J.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 14:46 ` Rich Shepard <[email protected]>
2025-02-04 20:57 ` Re: Lookup tables [FIXED] Rich Shepard <[email protected]>
5 siblings, 1 reply; 27+ messages in thread
From: Rich Shepard @ 2025-02-04 14:46 UTC (permalink / raw)
To: pgsql-general
On Tue, 4 Feb 2025, Rob Sargent wrote:
> I would definitely add an Id for each of the looked up values. The code
> can use the Id (for the join or lookup) and the string value can safely be
> changed (think typo) without ripple effect. It also prevents other tables
> from referencing the lookup witrh bogus values (those not in the lookup
> table)
>
> Unless your lookup tables are huge I would create a new table matching
> your current table but with an identity column and load from you original
> table.
> https://www.postgresql.org/docs/current/ddl-identity-columns.html
Thanks, Rob. Each lookup table has only a very few rows,
I'll make new tables today.
Regards,
Rich
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables [FIXED]
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:46 ` Re: Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 20:57 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Rich Shepard @ 2025-02-04 20:57 UTC (permalink / raw)
To: pgsql-general
On Tue, 4 Feb 2025, Rich Shepard wrote:
> I'll make new tables today.
That did not work as well as I expected. Instead, I added a new column (type
`serial') to each of the two lookup tables. That adds a PK to each while
retaining the table and column names referenced by other tables.
Thanks for all your comments,
Rich
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 14:59 ` Ron Johnson <[email protected]>
2025-02-04 15:04 ` Re: Lookup tables David G. Johnston <[email protected]>
2025-02-04 15:05 ` Re: Lookup tables Rich Shepard <[email protected]>
5 siblings, 2 replies; 27+ messages in thread
From: Ron Johnson @ 2025-02-04 14:59 UTC (permalink / raw)
To: pgsql-general
On Tue, Feb 4, 2025 at 9:28 AM Rich Shepard <[email protected]>
wrote:
> Should lookup tables have a numeric FK column as well as the description
> column?
>
Does your lookup table just have one column? (That's what your question
seems to imply, but that makes no sense, since the whole point of a lookup
table is to store some sort of a code in the "child" table instead of the
whole text of the description.)
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables Ron Johnson <[email protected]>
@ 2025-02-04 15:04 ` David G. Johnston <[email protected]>
2025-02-04 15:08 ` Re: Lookup tables Rich Shepard <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: David G. Johnston @ 2025-02-04 15:04 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; +Cc: pgsql-general
On Tuesday, February 4, 2025, Ron Johnson <[email protected]> wrote:
> On Tue, Feb 4, 2025 at 9:28 AM Rich Shepard <[email protected]>
> wrote:
>
>> Should lookup tables have a numeric FK column as well as the description
>> column?
>>
>
> Does your lookup table just have one column? (That's what your question
> seems to imply, but that makes no sense, since the whole point of a lookup
> table is to store some sort of a code in the "child" table instead of the
> whole text of the description.)
>
The point of a lookup table is to provide a unique list of authoritative
values for some purpose. Kinda like an enum. But having the label serve
as the unique value is reasonable - we only add surrogates for optimization.
David J.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables Ron Johnson <[email protected]>
2025-02-04 15:04 ` Re: Lookup tables David G. Johnston <[email protected]>
@ 2025-02-04 15:08 ` Rich Shepard <[email protected]>
2025-02-04 15:18 ` Re: Lookup tables David G. Johnston <[email protected]>
2025-02-04 15:19 ` Re: Lookup tables Ron Johnson <[email protected]>
0 siblings, 2 replies; 27+ messages in thread
From: Rich Shepard @ 2025-02-04 15:08 UTC (permalink / raw)
To: pgsql-general
On Tue, 4 Feb 2025, David G. Johnston wrote:
> The point of a lookup table is to provide a unique list of authoritative
> values for some purpose. Kinda like an enum. But having the label serve as
> the unique value is reasonable - we only add surrogates for optimization.
David,
The industrytypes table has 26 rows, the statustypes table has 8 rows. Your
explanation suggests that for this database adding a PK to each table adds
little, if anything.
Regards,
Rich
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables Ron Johnson <[email protected]>
2025-02-04 15:04 ` Re: Lookup tables David G. Johnston <[email protected]>
2025-02-04 15:08 ` Re: Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 15:18 ` David G. Johnston <[email protected]>
2025-02-04 15:41 ` Re: Lookup tables Rich Shepard <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: David G. Johnston @ 2025-02-04 15:18 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Tuesday, February 4, 2025, Rich Shepard <[email protected]> wrote:
> On Tue, 4 Feb 2025, David G. Johnston wrote:
>
> The point of a lookup table is to provide a unique list of authoritative
>> values for some purpose. Kinda like an enum. But having the label serve as
>> the unique value is reasonable - we only add surrogates for optimization.
>>
>
> David,
>
> The industrytypes table has 26 rows, the statustypes table has 8 rows. Your
> explanation suggests that for this database adding a PK to each table adds
> little, if anything.
>
It’s the FK side where the cost savings are experienced.
David J.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables Ron Johnson <[email protected]>
2025-02-04 15:04 ` Re: Lookup tables David G. Johnston <[email protected]>
2025-02-04 15:08 ` Re: Lookup tables Rich Shepard <[email protected]>
2025-02-04 15:18 ` Re: Lookup tables David G. Johnston <[email protected]>
@ 2025-02-04 15:41 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Rich Shepard @ 2025-02-04 15:41 UTC (permalink / raw)
To: pgsql-general
On Tue, 4 Feb 2025, David G. Johnston wrote:
> It’s the FK side where the cost savings are experienced.
David,
Okay.
Thanks,
Rich
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables Ron Johnson <[email protected]>
2025-02-04 15:04 ` Re: Lookup tables David G. Johnston <[email protected]>
2025-02-04 15:08 ` Re: Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 15:19 ` Ron Johnson <[email protected]>
2025-02-04 16:06 ` Re: Lookup tables Adrian Klaver <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Ron Johnson @ 2025-02-04 15:19 UTC (permalink / raw)
To: pgsql-general
On Tue, Feb 4, 2025 at 10:08 AM Rich Shepard <[email protected]>
wrote:
> On Tue, 4 Feb 2025, David G. Johnston wrote:
>
> > The point of a lookup table is to provide a unique list of authoritative
> > values for some purpose. Kinda like an enum. But having the label serve
> as
> > the unique value is reasonable - we only add surrogates for optimization.
>
> David,
>
> The industrytypes table has 26 rows, the statustypes table has 8 rows. Your
> explanation suggests that for this database adding a PK to each table adds
> little, if anything.
>
How big is the database? A tiny 500MB db just for you can get by with poor
design. (But then, why are you using PG instead of SQLite?)
More importantly, will you ever update the descriptions? Of course not!
Famous last words. Having a separate PK means that you update one row in
one column, while what you've done means that tens/hundreds of thousands of
rows in possibly dozens of tables need to be updated.
It also means that *you* can easily change things in your ad hoc
database *without
forgetting* to update a table.
This is called an "update anomaly" in relational design theory.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables Ron Johnson <[email protected]>
2025-02-04 15:04 ` Re: Lookup tables David G. Johnston <[email protected]>
2025-02-04 15:08 ` Re: Lookup tables Rich Shepard <[email protected]>
2025-02-04 15:19 ` Re: Lookup tables Ron Johnson <[email protected]>
@ 2025-02-04 16:06 ` Adrian Klaver <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Adrian Klaver @ 2025-02-04 16:06 UTC (permalink / raw)
To: Ron Johnson <[email protected]>; pgsql-general
On 2/4/25 07:19, Ron Johnson wrote:
> How big is the database? A tiny 500MB db just for you can get by with
> poor design. (But then, why are you using PG instead of SQLite?)
For the reasons listed here:
https://sqlite.org/quirks.html
In particular:
[...]
2. SQLite Is Embedded, Not Client-Server
3. Flexible Typing
SQLite is flexible with regard to datatypes. Datatypes are advisory
rather than mandatory.
4. Foreign Key Enforcement Is Off By Default
[...]
>
> More importantly, will you ever update the descriptions? Of course not!
> Famous last words. Having a separate PK means that you update one row
> in one column, while what you've done means that tens/hundreds of
> thousands of rows in possibly dozens of tables need to be updated.
>
> It also means that *you* can easily change things in your ad hoc
> database *without forgetting* to update a table.
>
> This is called an "update anomaly" in relational design theory.
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables Ron Johnson <[email protected]>
@ 2025-02-04 15:05 ` Rich Shepard <[email protected]>
2025-02-04 15:12 ` Re: Lookup tables Ron Johnson <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Rich Shepard @ 2025-02-04 15:05 UTC (permalink / raw)
To: pgsql-general
On Tue, 4 Feb 2025, Ron Johnson wrote:
> Does your lookup table just have one column? (That's what your question
> seems to imply, but that makes no sense, since the whole point of a lookup
> table is to store some sort of a code in the "child" table instead of the
> whole text of the description.)
Ron,
Yes, each has a single column of type names, industrytypes and statustypes.
I've always used the descriptive names in queries.
Rich
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:59 ` Re: Lookup tables Ron Johnson <[email protected]>
2025-02-04 15:05 ` Re: Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 15:12 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Ron Johnson @ 2025-02-04 15:12 UTC (permalink / raw)
To: pgsql-general
On Tue, Feb 4, 2025 at 10:05 AM Rich Shepard <[email protected]>
wrote:
> On Tue, 4 Feb 2025, Ron Johnson wrote:
>
> > Does your lookup table just have one column? (That's what your question
> > seems to imply, but that makes no sense, since the whole point of a
> lookup
> > table is to store some sort of a code in the "child" table instead of the
> > whole text of the description.)
>
> Ron,
>
> Yes, each has a single column of type names, industrytypes and statustypes.
> I've always used the descriptive names in queries.
>
Yeah, that's definitely Bad Design, for the reason David enumerated.
In the lookup table, the PK can be either synthetic (an integer that means
nothing) or natural (a short text code, typically four characters) that is
an abbreviation of the description.
Synthetic keys have been the norm for the past 25+ years, but I have a soft
spot for natural keys.
Natural keys become unwieldy, though, when more than two columns are
required to define primality.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 15:11 ` Tony Shelver <[email protected]>
5 siblings, 0 replies; 27+ messages in thread
From: Tony Shelver @ 2025-02-04 15:11 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; pgadmin-support lists.postgresql.org <[email protected]>
Seen the suggestions.
After decades of playing with the alternatives, I don't overthink it
anymore. Pretty much my lookup tables consist of an autogenerated primary
key (smallint or integer), a code (name?) and a description as default.
If there are a lot of tables fitting a common data format, at times I have
consolidated them into a single table with a 'lookup type' column.
Depending on application requirements, sometimes there I will provide short
and long display names, and a generic description for further
clarification, but usually those requirements are overkill.
Tony
On Tue, 4 Feb 2025 at 16:28, Rich Shepard <[email protected]> wrote:
> Should lookup tables have a numeric FK column as well as the description
> column?
>
> If so, how should I add an FK to the two lookup tables in my database?
>
> TIA,
>
> Rich
>
>
>
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 16:31 ` Michał Kłeczek <[email protected]>
2025-02-04 16:51 ` Re: Lookup tables Karsten Hilbert <[email protected]>
2025-02-04 17:03 ` Re: Lookup tables Ron Johnson <[email protected]>
2025-02-04 17:11 ` Re: Lookup tables David G. Johnston <[email protected]>
5 siblings, 3 replies; 27+ messages in thread
From: Michał Kłeczek @ 2025-02-04 16:31 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
> On 4 Feb 2025, at 15:27, Rich Shepard <[email protected]> wrote:
>
> Should lookup tables have a numeric FK column as well as the description column?
>
> If so, how should I add an FK to the two lookup tables in my database?
I’ve read the whole thread and the reasoning for having (numeric) autogenerated surrogate key is:
a) performance
b) no cascading updates
I would like to add another dimension to this discussion: logical consistency.
Imagine the following simplified schema with surrogate PK:
create table restaurant (
restaurant_id int not null primary key generated always as identity,
name text not null unique
);
create table restaurant_visit (
visit_id int not null primary key generated always as identity,
guest_username text not null,
when date not null,
restaurant_id not null foreign key restaurant(restaurant_id),
rating smallint not null
);
Let’s say users on your website register restaurant visits and rate them.
The query to register a visit is:
insert into restaurant_visit
select $user, current_date, restaurant_id, $rating
from restaurant where name = $restaurant_name
It is now completely unclear what it means to change the name of the restaurant for already registered visits.
Is it still the same restaurant with a different name or a different restaurant?
Or let say someone swaps names of two restaurants.
That means a user that goes to the same restaurant every day would register visits to two different restaurants!
Using the name of a restaurant as primary key gets rid of these logical anomalies because
the database model now reflects facts from reality.
Having surrogate keys makes your relational database more like a network/object oriented database where rows don’t represent facts but rather some entities that have identity independent from their attributes.
Thanks,
Michal
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 16:31 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-04 16:51 ` Karsten Hilbert <[email protected]>
2025-02-04 16:57 ` Re: Lookup tables Rob Sargent <[email protected]>
2 siblings, 1 reply; 27+ messages in thread
From: Karsten Hilbert @ 2025-02-04 16:51 UTC (permalink / raw)
To: [email protected]; pgsql-general
Am Tue, Feb 04, 2025 at 05:31:13PM +0100 schrieb Michał Kłeczek:
> It is now completely unclear what it means to change the name of the restaurant for already registered visits.
> Is it still the same restaurant with a different name or a different restaurant?
>
> Or let say someone swaps names of two restaurants.
> That means a user that goes to the same restaurant every day would register visits to two different restaurants!
>
> Using the name of a restaurant as primary key gets rid of these logical anomalies because
> the database model now reflects facts from reality.
Reality tends to become so ambiguous as to not be
reflectable (two entirely different restaurants eventually,
within the flow of time, carry the very same name).
A primary key is very likely not the proper place to reflect
arbitrary business logic (is it the same restaurant or not ?
what if two restaurants have the same name at the same time
?). Primary keys are tools at the technical level.
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 16:31 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 16:51 ` Re: Lookup tables Karsten Hilbert <[email protected]>
@ 2025-02-04 16:57 ` Rob Sargent <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Rob Sargent @ 2025-02-04 16:57 UTC (permalink / raw)
To: [email protected]
On 2/4/25 09:51, Karsten Hilbert wrote:
> Am Tue, Feb 04, 2025 at 05:31:13PM +0100 schrieb Michał Kłeczek:
>
>> It is now completely unclear what it means to change the name of the restaurant for already registered visits.
>> Is it still the same restaurant with a different name or a different restaurant?
>>
>> Or let say someone swaps names of two restaurants.
>> That means a user that goes to the same restaurant every day would register visits to two different restaurants!
>>
>> Using the name of a restaurant as primary key gets rid of these logical anomalies because
>> the database model now reflects facts from reality.
> Reality tends to become so ambiguous as to not be
> reflectable (two entirely different restaurants eventually,
> within the flow of time, carry the very same name).
>
> A primary key is very likely not the proper place to reflect
> arbitrary business logic (is it the same restaurant or not ?
> what if two restaurants have the same name at the same time
> ?). Primary keys are tools at the technical level.
>
> Karsten
> --
> GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
>
>
That OP is using a single column is interesting: Taking this notion to
the restaurant/visit theme, the list of names or restaurants becomes a
proper table 'restaurant' (name, addresss, phone, website, etc). The
name is as useless as a key as first/last is for person.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 16:31 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-04 17:03 ` Ron Johnson <[email protected]>
2025-02-04 17:05 ` Re: Lookup tables Rob Sargent <[email protected]>
2 siblings, 1 reply; 27+ messages in thread
From: Ron Johnson @ 2025-02-04 17:03 UTC (permalink / raw)
To: pgsql-general
On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek <[email protected]> wrote:
[snip]
>
> The query to register a visit is:
> insert into restaurant_visit
> select $user, current_date, restaurant_id, $rating
> from restaurant where name = $restaurant_name
>
>
> It is now completely unclear what it means to change the name of the
> restaurant for already registered visits.
> Is it still the same restaurant with a different name or a different
> restaurant?
>
> Or let say someone swaps names of two restaurants.
> That means a user that goes to the same restaurant every day would
> register visits to two different restaurants!
>
Valid concerns, which means that you add a new restaurant record when the
name changes.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 16:31 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 17:03 ` Re: Lookup tables Ron Johnson <[email protected]>
@ 2025-02-04 17:05 ` Rob Sargent <[email protected]>
2025-02-04 17:10 ` Re: Lookup tables Ron Johnson <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Rob Sargent @ 2025-02-04 17:05 UTC (permalink / raw)
To: [email protected]
On 2/4/25 10:03, Ron Johnson wrote:
> On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek <[email protected]> wrote:
> [snip]
>
>
> The query to register a visit is:
> insert into restaurant_visit
> select $user, current_date, restaurant_id, $rating
> from restaurant where name = $restaurant_name
>
>
> It is now completely unclear what it means to change the name of
> the restaurant for already registered visits.
> Is it still the same restaurant with a different name or a
> different restaurant?
>
> Or let say someone swaps names of two restaurants.
> That means a user that goes to the same restaurant every day would
> register visits to two different restaurants!
>
>
> Valid concerns, which means that you add a new restaurant record when
> the name changes.
>
And there goes your unique index on phone number :)
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 16:31 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 17:03 ` Re: Lookup tables Ron Johnson <[email protected]>
2025-02-04 17:05 ` Re: Lookup tables Rob Sargent <[email protected]>
@ 2025-02-04 17:10 ` Ron Johnson <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Ron Johnson @ 2025-02-04 17:10 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
On Tue, Feb 4, 2025 at 12:05 PM Rob Sargent <[email protected]> wrote:
>
>
>
> On 2/4/25 10:03, Ron Johnson wrote:
>
> On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek <[email protected]> wrote:
> [snip]
>
>>
>> The query to register a visit is:
>> insert into restaurant_visit
>> select $user, current_date, restaurant_id, $rating
>> from restaurant where name = $restaurant_name
>>
>>
>> It is now completely unclear what it means to change the name of the
>> restaurant for already registered visits.
>> Is it still the same restaurant with a different name or a different
>> restaurant?
>>
>> Or let say someone swaps names of two restaurants.
>> That means a user that goes to the same restaurant every day would
>> register visits to two different restaurants!
>>
>
> Valid concerns, which means that you add a new restaurant record when the
> name changes.
>
> And there goes your unique index on phone number :)
>
I don't think I'd ever do that, since phone numbers can get reassigned.
Capital-L Large and old businesses probably don't even have unique indices
on SSN.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 16:31 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-04 17:11 ` David G. Johnston <[email protected]>
2025-02-05 17:27 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2 siblings, 1 reply; 27+ messages in thread
From: David G. Johnston @ 2025-02-04 17:11 UTC (permalink / raw)
To: Michał Kłeczek <[email protected]>; +Cc: Rich Shepard <[email protected]>; pgsql-general
On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek <[email protected]> wrote:
>
>
> > On 4 Feb 2025, at 15:27, Rich Shepard <[email protected]> wrote:
> >
> > Should lookup tables have a numeric FK column as well as the description
> column?
> >
> > If so, how should I add an FK to the two lookup tables in my database?
>
> I’ve read the whole thread and the reasoning for having (numeric)
> autogenerated surrogate key is:
> a) performance
> b) no cascading updates
>
> I would like to add another dimension to this discussion: logical
> consistency.
> Using the name of a restaurant as primary key gets rid of these logical
> anomalies because
> the database model now reflects facts from reality.
>
Well, we were talking about lookup tables and not entity modelling...
> Having surrogate keys makes your relational database more like a
> network/object oriented database where rows don’t represent facts but
> rather some entities that have identity independent from their attributes.
>
Exactly, this is why surrogate keys are not just inventions for performance
(when it comes to entities, not attribute lookups) but rather are necessary
because of the mutability of real-world objects.
My identity is separate from any single value of my attributes. Basically
any single thing about me can be changed but the coherent existence of my
"self" remains the same.
Frankly, the restaurant example the "Owner" of the business should probably
be considered part of its primary key - they don't announce "under new
ownership/management" just for fun - the new owner wants to keep the brand
recognition but discard historical opinions that are likely no longer true.
David J.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 16:31 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 17:11 ` Re: Lookup tables David G. Johnston <[email protected]>
@ 2025-02-05 17:27 ` Thiemo Kellner <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Thiemo Kellner @ 2025-02-05 17:27 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
04.02.2025 18:12:02 David G. Johnston <[email protected]>:
> On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek <[email protected]> wrote:
>
> Well, we were talking about lookup tables and not entity modelling...
I am under the impression that a lookup table IS an entity. You find them in star and snowflake models alike.
>
>>
>> Having surrogate keys makes your relational database more like a network/object oriented database where rows don’t represent facts but rather some entities that have identity independent from their attributes.
>
The presence or implementation of surrogate keys do not define in the least the type of database. It sole purpose is to surrogate the (speaking) business key such that updates on that key (think of typo) does not end up in an update orgy. Ok, maybe to simplify matters if you business key is made of more than one attribute/column. IMHO it is very good practice to still build a unique key on the business key and place a not-null-constraint on all its attributes.
>
>
> My identity is separate from any single value of my attributes. Basically any single thing about me can be changed but the coherent existence of my "self" remains the same.
I would not go that transcendently far, but my attributes change but it is still me, even though my age increases over time as do my good looks. ;-)
>
> Frankly, the restaurant example the "Owner" of the business should probably be considered part of its primary key - they don't announce "under new ownership/management" just for fun - the new owner wants to keep the brand recognition but discard historical opinions that are likely no longer true.
I'd prefer the term business key instead of primary key here, as, if you choose to use a surrogate key, that one becomes the PK while the BK is a UQ. ;-) Sorry, I got carried away. Having said that, I would leave the decision of taking the owner into the BK to the project. E.g. if you want to have the information of unbroken existence of a restaurant at a certain place, I dare say, it cannot be part of the BK. One could even argue that not even the name is part of the BK but only the geolocation (addresses can change too).
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 19:08 ` Rich Shepard <[email protected]>
2025-02-04 19:11 ` Re: Lookup tables David G. Johnston <[email protected]>
5 siblings, 1 reply; 27+ messages in thread
From: Rich Shepard @ 2025-02-04 19:08 UTC (permalink / raw)
To: pgsql-general
On Tue, 4 Feb 2025, Rob Sargent wrote:
> Unless your lookup tables are huge I would create a new table matching
> your current table but with an identity column and load from you original
> table.
I created a new table:
create table ind_types_lu (
ind_nbr serial primary key,
ind_name varchar(32) not null
);
Now the database has the new table and a new sequence:
public | ind_types_lu | table | rshepard
public | ind_types_lu_ind_nbr_seq | sequence | rshepard
I want to replace the old lookup table (with no FK) with this one. Can I
use:
alter table rename ind_types_lu to industrytypes;
and have the sequence name changed, too?
TIA,
Rich
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 19:08 ` Re: Lookup tables Rich Shepard <[email protected]>
@ 2025-02-04 19:11 ` David G. Johnston <[email protected]>
2025-02-04 19:33 ` Re: Lookup tables Rich Shepard <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: David G. Johnston @ 2025-02-04 19:11 UTC (permalink / raw)
To: Rich Shepard <[email protected]>; +Cc: pgsql-general
On Tuesday, February 4, 2025, Rich Shepard <[email protected]> wrote:
>
> I want to replace the old lookup table (with no FK) with this one.
>
Is the use of FK here intentional or a typo? Because everything written so
far leads me to believe it should be PK. Pri,are key is the unique side,
Foreign key is the usage side.
David J.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Lookup tables
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 19:08 ` Re: Lookup tables Rich Shepard <[email protected]>
2025-02-04 19:11 ` Re: Lookup tables David G. Johnston <[email protected]>
@ 2025-02-04 19:33 ` Rich Shepard <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Rich Shepard @ 2025-02-04 19:33 UTC (permalink / raw)
To: pgsql-general
On Tue, 4 Feb 2025, David G. Johnston wrote:
> Is the use of FK here intentional or a typo?
Sigh, typo. Should be PK.
Rich
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2025-02-05 17:27 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-04 14:27 Lookup tables Rich Shepard <[email protected]>
2025-02-04 14:41 ` David G. Johnston <[email protected]>
2025-02-04 14:56 ` Ron Johnson <[email protected]>
2025-02-04 14:59 ` David G. Johnston <[email protected]>
2025-02-04 14:46 ` Rich Shepard <[email protected]>
2025-02-04 20:57 ` Re: Lookup tables [FIXED] Rich Shepard <[email protected]>
2025-02-04 14:59 ` Ron Johnson <[email protected]>
2025-02-04 15:04 ` David G. Johnston <[email protected]>
2025-02-04 15:08 ` Rich Shepard <[email protected]>
2025-02-04 15:18 ` David G. Johnston <[email protected]>
2025-02-04 15:41 ` Rich Shepard <[email protected]>
2025-02-04 15:19 ` Ron Johnson <[email protected]>
2025-02-04 16:06 ` Adrian Klaver <[email protected]>
2025-02-04 15:05 ` Rich Shepard <[email protected]>
2025-02-04 15:12 ` Ron Johnson <[email protected]>
2025-02-04 15:11 ` Tony Shelver <[email protected]>
2025-02-04 16:31 ` Michał Kłeczek <[email protected]>
2025-02-04 16:51 ` Karsten Hilbert <[email protected]>
2025-02-04 16:57 ` Rob Sargent <[email protected]>
2025-02-04 17:03 ` Ron Johnson <[email protected]>
2025-02-04 17:05 ` Rob Sargent <[email protected]>
2025-02-04 17:10 ` Ron Johnson <[email protected]>
2025-02-04 17:11 ` David G. Johnston <[email protected]>
2025-02-05 17:27 ` Thiemo Kellner <[email protected]>
2025-02-04 19:08 ` Rich Shepard <[email protected]>
2025-02-04 19:11 ` David G. Johnston <[email protected]>
2025-02-04 19:33 ` Rich Shepard <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox