public inbox for [email protected]
help / color / mirror / Atom feedRe: Lookup tables
14+ messages / 5 participants
[nested] [flat]
* Re: Lookup tables
@ 2025-02-04 17:27 Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Thiemo Kellner @ 2025-02-04 17:27 UTC (permalink / raw)
To: [email protected]
Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface.
Cheers
Thiemo
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
@ 2025-02-04 17:30 ` Michał Kłeczek <[email protected]>
2025-02-04 17:52 ` Re: Lookup tables Karsten Hilbert <[email protected]>
2025-02-04 18:20 ` Re: Lookup tables David G. Johnston <[email protected]>
2025-02-04 19:03 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 21:41 ` Re: Lookup tables Thiemo Kellner <[email protected]>
0 siblings, 4 replies; 14+ messages in thread
From: Michał Kłeczek @ 2025-02-04 17:30 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; +Cc: [email protected]
> On 4 Feb 2025, at 18:27, Thiemo Kellner <[email protected]> wrote:
>
> Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface.
That is even worse because it ceases being transactional and users might select something different than what they see on the screen.
—
Michał
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-04 17:52 ` Karsten Hilbert <[email protected]>
3 siblings, 0 replies; 14+ messages in thread
From: Karsten Hilbert @ 2025-02-04 17:52 UTC (permalink / raw)
To: [email protected]
Am Tue, Feb 04, 2025 at 06:30:53PM +0100 schrieb Michał Kłeczek:
> > On 4 Feb 2025, at 18:27, Thiemo Kellner <[email protected]> wrote:
> >
> > Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface.
>
> That is even worse because it ceases being transactional and users might select something different than what they see on the screen.
I never thought of that, thanks.
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-04 18:20 ` David G. Johnston <[email protected]>
3 siblings, 0 replies; 14+ messages in thread
From: David G. Johnston @ 2025-02-04 18:20 UTC (permalink / raw)
To: Michał Kłeczek <[email protected]>; +Cc: Thiemo Kellner <[email protected]>; [email protected] <[email protected]>
On Tuesday, February 4, 2025, Michał Kłeczek <[email protected]> wrote:
>
> > On 4 Feb 2025, at 18:27, Thiemo Kellner <[email protected]>
> wrote:
> >
> > Unless the lookup table is actually a check constraint one can use to
> populate dropdown boxes in an interface.
>
> That is even worse because it ceases being transactional and users might
> select something different than what they see on the screen.
>
What is the process flow for this happening? How big a bug needs to exist
in the system for something bad to happen as opposed to some kind of “value
not found” error and a refresh with the correct matching values showing up
in the UI?
David J.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-04 19:03 ` Thiemo Kellner <[email protected]>
3 siblings, 0 replies; 14+ messages in thread
From: Thiemo Kellner @ 2025-02-04 19:03 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
04.02.2025 18:31:09 Michał Kłeczek <[email protected]>:
>> Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface.
>
> That is even worse because it ceases being transactional and users might select something different than what they see on the screen.
In how far is a real check constraint less transactional? And in how far is it more advisable to have a real check constraint and fill your dropdown boxes from another source and having to keep that source on sync with the real check constraint?
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-04 21:41 ` Thiemo Kellner <[email protected]>
2025-02-04 21:44 ` Re: Lookup tables Karsten Hilbert <[email protected]>
2025-02-05 12:55 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-06 21:03 ` Re: Lookup tables Peter J. Holzer <[email protected]>
3 siblings, 3 replies; 14+ messages in thread
From: Thiemo Kellner @ 2025-02-04 21:41 UTC (permalink / raw)
To: pgsql-generallists.postgresql.org <[email protected]>
04.02.2025 18:31:09 Michał Kłeczek <[email protected]>:
>
>> On 4 Feb 2025, at 18:27, Thiemo Kellner <[email protected]> wrote:
>>
>> Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface.
>
> That is even worse because it ceases being transactional and users might select something different than what they see on the screen.
I might see what you want to point out. E.g. the table is COLOURS. The rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. Now you load these values into the dropdown box that sports RED, BLUE, GREE and so on. While someone selects GREE, there is a maintenance release changing GREE to YELLOW. So when that someone sends the selection by id to the backend, not GREE is selected but YELLOW.
A) Your release changed the sementics of the record 3. It's meaning changed. I cannot recommend doing that.
B) If you absolutely must change the semantic, put your application into maintenance mode in which noone can select anything beforehand.
If the maintenance would just correct the typo from GREE to GREEN, nothing would happen. Yor customer still ordered the lavishly green E-Bike her hear ever desired.
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 21:41 ` Re: Lookup tables Thiemo Kellner <[email protected]>
@ 2025-02-04 21:44 ` Karsten Hilbert <[email protected]>
2 siblings, 0 replies; 14+ messages in thread
From: Karsten Hilbert @ 2025-02-04 21:44 UTC (permalink / raw)
To: [email protected]
Am Tue, Feb 04, 2025 at 10:41:38PM +0100 schrieb Thiemo Kellner:
> >> On 4 Feb 2025, at 18:27, Thiemo Kellner <[email protected]> wrote:
> >>
> >> Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface.
> >
> > That is even worse because it ceases being transactional and users might select something different than what they see on the screen.
>
> I might see what you want to point out. E.g. the table is COLOURS. The rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. Now you load these values into the dropdown box that sports RED, BLUE, GREE and so on. While someone selects GREE, there is a maintenance release changing GREE to YELLOW. So when that someone sends the selection by id to the backend, not GREE is selected but YELLOW.
Yep, that's what I meant and which I never thought of before.
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 21:41 ` Re: Lookup tables Thiemo Kellner <[email protected]>
@ 2025-02-05 12:55 ` Michał Kłeczek <[email protected]>
2025-02-05 20:33 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2 siblings, 1 reply; 14+ messages in thread
From: Michał Kłeczek @ 2025-02-05 12:55 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; +Cc: pgsql-general <[email protected]>
> On 4 Feb 2025, at 22:41, Thiemo Kellner <[email protected]> wrote:
>
> 04.02.2025 18:31:09 Michał Kłeczek <[email protected]>:
>
>>
>>> On 4 Feb 2025, at 18:27, Thiemo Kellner <[email protected]> wrote:
>>>
>>> Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface.
>>
>> That is even worse because it ceases being transactional and users might select something different than what they see on the screen.
>
> I might see what you want to point out. E.g. the table is COLOURS. The rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on. Now you load these values into the dropdown box that sports RED, BLUE, GREE and so on. While someone selects GREE, there is a maintenance release changing GREE to YELLOW. So when that someone sends the selection by id to the backend, not GREE is selected but YELLOW.
>
> A) Your release changed the sementics of the record 3. It's meaning changed. I cannot recommend doing that.
That’s what using natural keys and FK’s restricting their changes guarantee: no (accidental) changes to meaning of data.
Even with cascading updates you still have transactional semantics (ie. the user selects what’s on the screen or gets an error).
> B) If you absolutely must change the semantic, put your application into maintenance mode in which noone can select anything beforehand.
All this error prone hassle and downtime can be avoided with natural keys and guarantees that DBMS gives you.
>
> If the maintenance would just correct the typo from GREE to GREEN, nothing would happen. Yor customer still ordered the lavishly green E-Bike her hear ever desired.
The question is: how do you _ensure_ that?
—
Michal
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 21:41 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-05 12:55 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-05 20:33 ` Thiemo Kellner <[email protected]>
2025-02-05 21:19 ` Re: Lookup tables Michał Kłeczek <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Thiemo Kellner @ 2025-02-05 20:33 UTC (permalink / raw)
To: pgsql-general <[email protected]>
El 05-02-25 a las 13:55, Michał Kłeczek escribió:
>> A) Your release changed the sementics of the record 3. It's meaning changed. I cannot recommend doing that.
> That’s what using natural keys and FK’s restricting their changes guarantee: no (accidental) changes to meaning of data.
> Even with cascading updates you still have transactional semantics (ie. the user selects what’s on the screen or gets an error).
Sorry, that is utter nonsense. You cannot ever guarantee an update does
not mess up the semantics on the updated field, change the meaning. You
would need a check constraint which in it turn needs to get set up where
one can mess up things.
>> B) If you absolutely must change the semantic, put your application into maintenance mode in which noone can select anything beforehand.
> All this error prone hassle and downtime can be avoided with natural keys and guarantees that DBMS gives you.
And I thought you would have denied the need of changing semantics
above. And no, changing your natural keys semantically ALWAYS requires
downtime to make sure you do not run into the race condition described
above.
>> If the maintenance would just correct the typo from GREE to GREEN, nothing would happen. Yor customer still ordered the lavishly green E-Bike her hear ever desired.
> The question is: how do you _ensure_ that?
Ensure, the update goes from GREE to GREEN? You cannot, simple as that.
You just can minimize the risk by testing, testing, testing. But that
holds equally true for the business key of a surrogate key table as
natural key table. That's why the surrogate key is such an elegant
construct. You can change business key of the record with id 3 from GREE
to GREEN, VERT, GRÜN, VERDE or ASéLDHK()*NSLDFHP)(*Z . It keeps its
meaning of the perception of the human eye of electromagnetic waves of
the wavelength roughly between 495-570 nm (according to Wikipedia).
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 21:41 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-05 12:55 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-05 20:33 ` Re: Lookup tables Thiemo Kellner <[email protected]>
@ 2025-02-05 21:19 ` Michał Kłeczek <[email protected]>
2025-02-05 22:36 ` Re: Lookup tables Thiemo Kellner <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Michał Kłeczek @ 2025-02-05 21:19 UTC (permalink / raw)
To: Thiemo Kellner <[email protected]>; +Cc: pgsql-general <[email protected]>
> On 5 Feb 2025, at 21:33, Thiemo Kellner <[email protected]> wrote:
>
>
> El 05-02-25 a las 13:55, Michał Kłeczek escribió:
>>> A) Your release changed the sementics of the record 3. It's meaning changed. I cannot recommend doing that.
>> That’s what using natural keys and FK’s restricting their changes guarantee: no (accidental) changes to meaning of data.
>> Even with cascading updates you still have transactional semantics (ie. the user selects what’s on the screen or gets an error).
> Sorry, that is utter nonsense. You cannot ever guarantee an update does not mess up the semantics on the updated field, change the meaning. Y
But you can guarantee that if you change the value of the key after the user displays it - the user will get an error on submission (whereas with the surrogate key it would happily proceed without user noticing).
> ou would need a check constraint which in it turn needs to get set up where one can mess up things.
>>> B) If you absolutely must change the semantic, put your application into maintenance mode in which noone can select anything beforehand.
>> All this error prone hassle and downtime can be avoided with natural keys and guarantees that DBMS gives you.
> And I thought you would have denied the need of changing semantics above. And no, changing your natural keys semantically ALWAYS requires downtime to make sure you do not run into the race condition described above.
How so? The user is going to get FK violation - you do not need any downtime to make sure users don’t submit wrong values.
>>> If the maintenance would just correct the typo from GREE to GREEN, nothing would happen. Yor customer still ordered the lavishly green E-Bike her hear ever desired.
>> The question is: how do you _ensure_ that?
> Ensure, the update goes from GREE to GREEN? You cannot, simple as that. You just can minimize the risk by testing, testing, testing.
You can also simply disallow updates with FK constraint eliminating risk.
> But that holds equally true for the business key of a surrogate key table as natural key table. That's why the surrogate key is such an elegant construct. You can change business key of the record with id 3 from GREE to GREEN, VERT, GRÜN, VERDE or ASéLDHK()*NSLDFHP)(*Z . It keeps its meaning of the perception of the human eye of electromagnetic waves of the wavelength roughly between 495-570 nm (according to Wikipedia).
And why do you think unconstrained updating of business key is a good thing?
You must implement rules governing what can and what cannot be changed *somewhere* - not doing it in the database means you have to do it in applications.
Anyway - let’s agree to disagree :)
—
Michal
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 21:41 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-05 12:55 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-05 20:33 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-05 21:19 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-05 22:36 ` Thiemo Kellner <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Thiemo Kellner @ 2025-02-05 22:36 UTC (permalink / raw)
To: pgsql-general <[email protected]>
El 05-02-25 a las 22:19, Michał Kłeczek escribió:
> But you can guarantee that if you change the value of the key after the user displays it - the user will get an error on submission (whereas with the surrogate key it would happily proceed without user noticing).
As you very rightly say happily proceed, because that is, what you
actually want. The user chose GREE meaning green, which is updated in
the meantime to GREEN. All good. Your solution throws an error for nothing.
> How so? The user is going to get FK violation - you do not need any downtime to make sure users don’t submit wrong values.
Sorry, I was not aware, your aim is to bother the customer with FK
violation messages, if you can avoid it.
>> Ensure, the update goes from GREE to GREEN? You cannot, simple as that. You just can minimize the risk by testing, testing, testing.
> You can also simply disallow updates with FK constraint eliminating risk.
This is nothing that is specific to surrogate or natural keys. If one
disallow updates, one has to live with typos and everything.
>> But that holds equally true for the business key of a surrogate key table as natural key table. That's why the surrogate key is such an elegant construct. You can change business key of the record with id 3 from GREE to GREEN, VERT, GRÜN, VERDE or ASéLDHK()*NSLDFHP)(*Z . It keeps its meaning of the perception of the human eye of electromagnetic waves of the wavelength roughly between 495-570 nm (according to Wikipedia).
> And why do you think unconstrained updating of business key is a good thing?
I am not sure what is your take on "unconstrained". As already
mentioned, testing is the only constraint you can set up. And I am
neither sure where your problem is with updating unless you have an
update orgy because your ref-constraint goes on natural keys.
> You must implement rules governing what can and what cannot be changed *somewhere* - not doing it in the database means you have to do it in applications.
That sounds adventurous. Maybe I am not seeing what you mean, but is
sound to me that you build a shadow database where you map your GREE to
GREEN in the application layer so the application can display the
correct value in the GUI, but no one has to update the core database.
And if there is a typo in the shadow data base you build another mapping
database on top of it?
> Anyway - let’s agree to disagree :)
Agreed :-)
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 21:41 ` Re: Lookup tables Thiemo Kellner <[email protected]>
@ 2025-02-06 21:03 ` Peter J. Holzer <[email protected]>
2025-02-07 08:22 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2 siblings, 1 reply; 14+ messages in thread
From: Peter J. Holzer @ 2025-02-06 21:03 UTC (permalink / raw)
To: [email protected]
On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote:
> 04.02.2025 18:31:09 Michał Kłeczek <[email protected]>:
>
> >
> >> On 4 Feb 2025, at 18:27, Thiemo Kellner <[email protected]> wrote:
> >>
> >> Unless the lookup table is actually a check constraint one
> >> can use to populate dropdown boxes in an interface.
> >
> > That is even worse because it ceases being transactional and users
> > might select something different than what they see on the screen.
>
> I might see what you want to point out. E.g. the table is COLOURS. The
> rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on.
> Now you load these values into the dropdown box that sports RED, BLUE,
> GREE and so on. While someone selects GREE, there is a maintenance
> release changing GREE to YELLOW. So when that someone sends the
> selection by id to the backend, not GREE is selected but YELLOW.
I fail to see why use of a surrogate key is the problem here.
Either changing the color from GREE to YELLOW makes sense or it doesn't.
If it doesn't make sense, then it's release which is faulty, not the
model.
if it does make sense (I'm a bit at a loss when that might be the case,
maybe the "color" is just a code word, or maybe they are colors in a
design which are arbitrary but must be consistent), then the experience
that the user has is exactly the same as if the maintenance release was
applied just after they selected the color. Which might be a bit
confusing but is almost certainly what is wanted.
> A) Your release changed the sementics of the record 3. It's meaning
> changed. I cannot recommend doing that.
If the release changed the semantics of an existing record the release
was almost certainly wrong.
> B) If you absolutely must change the semantic, put your application
> into maintenance mode in which noone can select anything beforehand.
>
> If the maintenance would just correct the typo from GREE to GREEN,
> nothing would happen. Yor customer still ordered the lavishly green
> E-Bike her hear ever desired.
Yeah, that's a good example where changing the color from GREE to YELLOW
doesn't make sense. Presumably that ID 3 is used as a foreign key in
lots of places, e,g. in an inventory table. Your bikes in stock won't
just magically change color just because you changed some text in the
database. So that change simply doesn't make sense and shouldn't be done
as part of a maintenance release. Confusing a few people who just happen
to open the dropdown in the wrong second is the least of your problems.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 21:41 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-06 21:03 ` Re: Lookup tables Peter J. Holzer <[email protected]>
@ 2025-02-07 08:22 ` Michał Kłeczek <[email protected]>
2025-02-07 18:42 ` Re: Lookup tables Peter J. Holzer <[email protected]>
0 siblings, 1 reply; 14+ messages in thread
From: Michał Kłeczek @ 2025-02-07 08:22 UTC (permalink / raw)
To: Peter J. Holzer <[email protected]>; +Cc: [email protected]
> On 6 Feb 2025, at 22:03, Peter J. Holzer <[email protected]> wrote:
>
> On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote:
>>
>> I might see what you want to point out. E.g. the table is COLOURS. The
>> rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on.
>> Now you load these values into the dropdown box that sports RED, BLUE,
>> GREE and so on. While someone selects GREE, there is a maintenance
>> release changing GREE to YELLOW. So when that someone sends the
>> selection by id to the backend, not GREE is selected but YELLOW.
>
> I fail to see why use of a surrogate key is the problem here.
>
> Either changing the color from GREE to YELLOW makes sense or it doesn't.
>
> If it doesn't make sense, then it's release which is faulty, not the
> model.
>
> if it does make sense (I'm a bit at a loss when that might be the case,
> maybe the "color" is just a code word, or maybe they are colors in a
> design which are arbitrary but must be consistent), then the experience
> that the user has is exactly the same as if the maintenance release was
> applied just after they selected the color. Which might be a bit
> confusing but is almost certainly what is wanted.
>
This is simply saying “what is implemented is certainly wanted, so what’s the point”.
The discussion is about *defining* what is wanted and using DBMS to *enforce* that.
>
>> A) Your release changed the sementics of the record 3. It's meaning
>> changed. I cannot recommend doing that.
>
> If the release changed the semantics of an existing record the release
> was almost certainly wrong.
Is it possible to minimize the risk of “wrong releases” using mechanisms that DBMS provides?
>
>> B) If you absolutely must change the semantic, put your application
>> into maintenance mode in which noone can select anything beforehand.
>>
>> If the maintenance would just correct the typo from GREE to GREEN,
>> nothing would happen. Yor customer still ordered the lavishly green
>> E-Bike her hear ever desired.
>
> Yeah, that's a good example where changing the color from GREE to YELLOW
> doesn't make sense. Presumably that ID 3 is used as a foreign key in
> lots of places, e,g. in an inventory table. Your bikes in stock won't
> just magically change color just because you changed some text in the
> database. So that change simply doesn't make sense and shouldn't be done
> as part of a maintenance release. Confusing a few people who just happen
> to open the dropdown in the wrong second is the least of your problems.
We are in agreement here. What I am also saying is:
having color name as PK and all FKs referencing it *prevents* these unwanted updates.
—
Michal
^ permalink raw reply [nested|flat] 14+ messages in thread
* Re: Lookup tables
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Re: Lookup tables Michał Kłeczek <[email protected]>
2025-02-04 21:41 ` Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-06 21:03 ` Re: Lookup tables Peter J. Holzer <[email protected]>
2025-02-07 08:22 ` Re: Lookup tables Michał Kłeczek <[email protected]>
@ 2025-02-07 18:42 ` Peter J. Holzer <[email protected]>
0 siblings, 0 replies; 14+ messages in thread
From: Peter J. Holzer @ 2025-02-07 18:42 UTC (permalink / raw)
To: [email protected]
On 2025-02-07 09:22:13 +0100, Michał Kłeczek wrote:
>
>
> On 6 Feb 2025, at 22:03, Peter J. Holzer <[email protected]> wrote:
>
> On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote:
>
>
> I might see what you want to point out. E.g. the table is COLOURS. The
> rec with id 1 is RED, the one with id 2 is BLUE, 3 is GREE and so on.
> Now you load these values into the dropdown box that sports RED, BLUE,
> GREE and so on. While someone selects GREE, there is a maintenance
> release changing GREE to YELLOW. So when that someone sends the
> selection by id to the backend, not GREE is selected but YELLOW.
>
>
> I fail to see why use of a surrogate key is the problem here.
>
> Either changing the color from GREE to YELLOW makes sense or it doesn't.
>
>
> If it doesn't make sense, then it's release which is faulty, not the
> model.
>
> if it does make sense (I'm a bit at a loss when that might be the case,
> maybe the "color" is just a code word, or maybe they are colors in a
> design which are arbitrary but must be consistent), then the experience
> that the user has is exactly the same as if the maintenance release was
> applied just after they selected the color. Which might be a bit
> confusing but is almost certainly what is wanted.
>
>
>
> This is simply saying “what is implemented is certainly wanted,
Not at all. It may or may not be what is wanted but we can't tell unless
we know what is wanted. We have a single table without any context. What
does this table represent? What is the identity here? If it's the color
itself, changing green to yellow doesn't make any sense ("it's the same
color, but now it's yellow instead of green" is self-contradictory). If
the identity has some application-defined semantics, it may make sense.
> so what’s the point”.
Indeed. Without knowing the semantics discussing the data model or the
advisability of certain updates is indeed rather pointless.
Disagreements will be more likely to be due to unstated assumptions
about the application than because of general principles.
> A) Your release changed the sementics of the record 3. It's meaning
> changed. I cannot recommend doing that.
>
>
> If the release changed the semantics of an existing record the release
> was almost certainly wrong.
>
>
> Is it possible to minimize the risk of “wrong releases” using mechanisms that
> DBMS provides?
Maybe. But the person who thought that
update colors set name = 'YELLOW' where id = 3; -- YELLOW is the new GREE
was a good idea might also think that
update doodads set color = 'YELLOW' where color = 'GREE';
is a good idea.
Is it? I don't know. Maybe it is. Maybe it isn't. There is no way to
know unless we know the requirements.
> If the maintenance would just correct the typo from GREE to GREEN,
> nothing would happen. Yor customer still ordered the lavishly green
> E-Bike her hear ever desired.
>
>
> Yeah, that's a good example where changing the color from GREE to YELLOW
> doesn't make sense. Presumably that ID 3 is used as a foreign key in
> lots of places, e,g. in an inventory table. Your bikes in stock won't
> just magically change color just because you changed some text in the
> database. So that change simply doesn't make sense and shouldn't be done
> as part of a maintenance release. Confusing a few people who just happen
> to open the dropdown in the wrong second is the least of your problems.
>
>
> We are in agreement here. What I am also saying is:
> having color name as PK and all FKs referencing it *prevents* these unwanted
> updates.
I'm not convinced.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | [email protected] | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (833B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 14+ messages in thread
end of thread, other threads:[~2025-02-07 18:42 UTC | newest]
Thread overview: 14+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-02-04 17:27 Re: Lookup tables Thiemo Kellner <[email protected]>
2025-02-04 17:30 ` Michał Kłeczek <[email protected]>
2025-02-04 17:52 ` Karsten Hilbert <[email protected]>
2025-02-04 18:20 ` David G. Johnston <[email protected]>
2025-02-04 19:03 ` Thiemo Kellner <[email protected]>
2025-02-04 21:41 ` Thiemo Kellner <[email protected]>
2025-02-04 21:44 ` Karsten Hilbert <[email protected]>
2025-02-05 12:55 ` Michał Kłeczek <[email protected]>
2025-02-05 20:33 ` Thiemo Kellner <[email protected]>
2025-02-05 21:19 ` Michał Kłeczek <[email protected]>
2025-02-05 22:36 ` Thiemo Kellner <[email protected]>
2025-02-06 21:03 ` Peter J. Holzer <[email protected]>
2025-02-07 08:22 ` Michał Kłeczek <[email protected]>
2025-02-07 18:42 ` Peter J. Holzer <[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