On 2025-02-07 09:22:13 +0100, Michał Kłeczek wrote:
>
>
> On 6 Feb 2025, at 22:03, Peter J. Holzer 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 on
06.02.2025 22:04:34 Peter J. Holzer :
>> 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 select
> On 6 Feb 2025, at 22:03, Peter J. Holzer 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
On 2025-02-04 22:41:38 +0100, Thiemo Kellner wrote:
> 04.02.2025 18:31:09 Michał Kłeczek :
>
> >
> >> On 4 Feb 2025, at 18:27, Thiemo Kellner
> >> wrote:
> >>
> >> Unless the lookup table is actually a check constraint one
> >> can use to populate dropdown boxes in an interface.
> >
> > Tha
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 happil
> On 5 Feb 2025, at 21:33, Thiemo Kellner 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 gu
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 cas
El 05-02-25 a las 19:13, Michał Kłeczek escribió:
Only if you do not see the primary key as the main immutable value identifying
an object, entity, you name it.
Surrogate key cannot identify any (real) object by definition :)
What object is identified by PK value 42 in “restaurants” table?
Wh
> On 5 Feb 2025, at 19:07, Thiemo Kellner wrote:
>
> El 04-02-25 a las 18:08, Michał Kłeczek escribió:
>>> 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 primar
El 04-02-25 a las 18:08, Michał Kłeczek escribió:
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 logi
04.02.2025 18:12:02 David G. Johnston :
> On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek 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 mod
> On 4 Feb 2025, at 22:41, Thiemo Kellner wrote:
>
> 04.02.2025 18:31:09 Michał Kłeczek :
>
>>
>>> On 4 Feb 2025, at 18:27, Thiemo Kellner wrote:
>>>
>>> Unless the lookup table is actually a check constraint one can use to
>>> populate dropdown boxes in an interface.
>>
>> That is eve
> On 4 Feb 2025, at 18:11, David G. Johnston wrote:
>
> On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek <mailto:mic...@kleczek.org>> wrote:
>>
>>
>> > On 4 Feb 2025, at 15:27, Rich Shepard > > <mailto:rshep...@appl-ecosys.com>> wrote:
&g
Am Tue, Feb 04, 2025 at 10:41:38PM +0100 schrieb Thiemo Kellner:
> >> On 4 Feb 2025, at 18:27, Thiemo Kellner
> >> 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
04.02.2025 18:31:09 Michał Kłeczek :
>
>> On 4 Feb 2025, at 18:27, Thiemo Kellner 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
> se
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 table
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
On Tuesday, February 4, 2025, Rich Shepard 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 usag
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
04.02.2025 18:31:09 Michał Kłeczek :
>> 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.
On Tuesday, February 4, 2025, Michał Kłeczek wrote:
>
> > On 4 Feb 2025, at 18:27, Thiemo Kellner
> 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 u
Am Tue, Feb 04, 2025 at 06:30:53PM +0100 schrieb Michał Kłeczek:
> > On 4 Feb 2025, at 18:27, Thiemo Kellner 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 transact
> On 4 Feb 2025, at 18:27, Thiemo Kellner 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 th
Unless the lookup table is actually a check constraint one can use to populate
dropdown boxes in an interface.
Cheers
Thiemo
On Tue, Feb 4, 2025 at 9:31 AM Michał Kłeczek wrote:
>
>
> > On 4 Feb 2025, at 15:27, Rich Shepard 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 t
On Tue, Feb 4, 2025 at 12:05 PM Rob Sargent wrote:
>
>
>
> On 2/4/25 10:03, Ron Johnson wrote:
>
> On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek wrote:
> [snip]
>
>>
>> The query to register a visit is:
>> insert into restaurant_visit
>> select $user, current_date, restaurant_id, $rating
>> fro
> On 4 Feb 2025, at 17: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 n
On 2/4/25 10:03, Ron Johnson wrote:
On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek 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 co
On Tue, Feb 4, 2025 at 11:31 AM Michał Kłeczek 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 nam
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 resta
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 na
> On 4 Feb 2025, at 15:27, Rich Shepard 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 (nume
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-Se
On Tue, Feb 4, 2025 at 9:28 AM Rich Shepard
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 l
On Tue, 4 Feb 2025, David G. Johnston wrote:
It’s the FK side where the cost savings are experienced.
David,
Okay.
Thanks,
Rich
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.)
R
On Tue, Feb 4, 2025 at 10:08 AM Rich Shepard
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 - w
On Tuesday, February 4, 2025, Rich Shepard 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 a
On Tue, Feb 4, 2025 at 10:05 AM Rich Shepard
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
On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston
wrote:
> On Tuesday, February 4, 2025, Rich Shepard
> 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 tabl
On Tuesday, February 4, 2025, Ron Johnson wrote:
> On Tue, Feb 4, 2025 at 9:28 AM Rich Shepard
> 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 wh
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 t
On Tue, Feb 4, 2025 at 9:59 AM David G. Johnston
wrote:
> On Tuesday, February 4, 2025, Ron Johnson wrote:
>
>> On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston <
>> david.g.johns...@gmail.com> wrote:
>>
>>> On Tuesday, February 4, 2025, Rich Shepard
&
On Tuesday, February 4, 2025, Ron Johnson wrote:
> On Tue, Feb 4, 2025 at 9:41 AM David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Tuesday, February 4, 2025, Rich Shepard
>> wrote:
>>
>>> Should lookup tables have a numeric FK co
On Tue, 4 Feb 2025, David G. Johnston wrote:
Most do (have a surrogate PK) since it removes cascading updates and is a
smaller value.
Lots of alter tables and update queries.
David,
That's a good point.
Thanks,
Rich
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
On Tuesday, February 4, 2025, Rich Shepard 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 a
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
48 matches
Mail list logo
