Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

> 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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

> 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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

> 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

Re: Lookup tables

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

Re: Lookup tables

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 models alike. > >> >> Havi

Re: Lookup tables

> 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

Re: Lookup tables

> On 4 Feb 2025, at 18:11, David G. Johnston wrote: > > 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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables [FIXED]

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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

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.

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

> 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

Re: Lookup tables

Unless the lookup table is actually a check constraint one can use to populate dropdown boxes in an interface. Cheers Thiemo

Re: Lookup tables

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 tables in my database? > > I’ve read the wh

Re: Lookup tables

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

Re: Lookup tables

> 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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

> 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 (numeric) autogenerated s

Re: Lookup tables

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

Re: Lookup tables

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 lookup table is t

Re: Lookup tables

On Tue, 4 Feb 2025, David G. Johnston wrote: It’s the FK side where the cost savings are experienced. David, Okay. Thanks, Rich

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

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

Re: Lookup tables

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 tables in my database? >> > > Most do (have

Re: Lookup tables

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 what your question > seems to imply, but t

Re: Lookup tables

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

Re: Lookup tables

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 >>> wrote: >>> Should lookup tables hav

Re: Lookup tables

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 column as well as the description >>> column? >>> >>>

Re: Lookup tables

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

Re: Lookup tables

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 b

Re: Lookup tables

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 and is a smaller va