Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps
 (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1jTfdl-00049c-TV
	for pgsql-admin@arkaria.postgresql.org; Wed, 29 Apr 2020 05:52:13 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1jTfdk-0003Ub-RB
	for pgsql-admin@arkaria.postgresql.org; Wed, 29 Apr 2020 05:52:12 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256)
	(Exim 4.92)
	(envelope-from <rui@crazybean.net>)
	id 1jTfdk-0003UU-Js
	for pgsql-admin@lists.postgresql.org; Wed, 29 Apr 2020 05:52:12 +0000
Received: from impout003aa.msg.chrl.nc.charter.net ([47.43.20.27]
 helo=impout003.msg.chrl.nc.charter.net)
	by makus.postgresql.org with esmtp (Exim 4.92)
	(envelope-from <rui@crazybean.net>)
	id 1jTfdi-0001ft-4a
	for pgsql-admin@lists.postgresql.org; Wed, 29 Apr 2020 05:52:11 +0000
Received: from cerberus.crazybean.net ([24.151.69.73])
	by cmsmtp with ESMTP
	id TfdgjXmLAQFrUTfdgjbxlN; Wed, 29 Apr 2020 05:52:08 +0000
Authentication-Results: crazybean.net; none
X-Authority-Analysis: v=2.3 cv=Xf89M7x5 c=1 sm=1 tr=0
 a=YXTuGtgWN9YX+aIhe8tYuA==:117 a=YXTuGtgWN9YX+aIhe8tYuA==:17
 a=xqWC_Br6kY4A:10 a=IkcTkHD0fZMA:10 a=cl8xLZFz6L8A:10 a=pGLkceISAAAA:8
 a=tObQHoz9X0-B09f_jvwA:9 a=HLJjfGM1xhgEC3Pm:21 a=WZYKnWNNQo9TZPYS:21
 a=QEXdDO2ut3YA:10
Received: from [IPv6:2001:470:e117:4:45f1:366f:a08a:8c7] (<unknown>
 [2001:470:e117:4:45f1:366f:a08a:8c7])
	by cerberus.crazybean.net (OpenSMTPD) with ESMTPSA id 85722c61
 (TLSv1.2:ECDHE-RSA-AES256-GCM-SHA384:256:NO);
	Wed, 29 Apr 2020 01:52:08 -0400 (EDT)
Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.80.23.2.2\))
Subject: Re: PostgreSQL CHARACTER VARYING vs CHARACTER VARYING (Length)
From: Rui DeSousa <rui@crazybean.net>
In-Reply-To: <87pnbqsvtw.fsf@gmail.com>
Date: Wed, 29 Apr 2020 01:52:07 -0400
Cc: pgsql-admin@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <1C2C0F91-BA6D-48F1-BA65-E91AF436E8E2@crazybean.net>
References: 
 <CAOasRJYCUwMYdd=BL29TupW_iCbvM3YRM01YTB_WYSUEays3CA@mail.gmail.com>
 <CAKhGwmB_C4a1XVrzYsr3CFmUxegZyvsC=7QtzkW6TP27J5G31w@mail.gmail.com>
 <20200428234332.7nkyomr2unlez6p5@raf.org>
 <8EC9D082-B541-435F-9EE3-3ED14AA3C2D1@crazybean.net>
 <20200429022928.fywdmiuc6oqpmgtc@raf.org>
 <D9F0D7D1-9D46-4B9B-8B62-EE139159F62E@crazybean.net>
 <87pnbqsvtw.fsf@gmail.com>
To: Tim Cross <theophilusx@gmail.com>
X-Mailer: Apple Mail (2.3608.80.23.2.2)
X-CMAE-Envelope: 
 MS4wfK+FKT5p1QrRWkdSdCD+0MhFmqmgyPkkn09mRAP+ecbyTfcaYylMwdltBbXAViGTNu+GDQkArr5Er/EB8GNke2+WQn229kzYNzsQ3WCQIxbuGYAP9TDX
 Ge6JmzS8Ji8Eq489wWA0atyocOagUTS4g1mGFG+GYbk437G3yvqpB11K/zeRlLHIidG1eELsutp9vLiFjjNRiqwfsMGnClf5rYUo8j4++FWG0H0LcfAeF6sd
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Precedence: bulk



> On Apr 29, 2020, at 1:30 AM, Tim Cross <theophilusx@gmail.com> wrote:
>=20
> I think the key term in this thread is 'arbitrary'. When implementing =
a
> schema design, it should reflect the known constraints inherent in the
> model, but it should avoid imposing arbitrary constraints if none =
exist
> or cannot be determined.
>=20
> So, if you know that a customer ID field has a current limitation of =
50
> characters, then use a definition which reflects that. It may be that =
at
> some point in the future, this will be increased, but then again, it =
may
> not and that bit of information provides useful information for
> application developers and helps with consistency across APIs. Without
> some guideline, different developers will impose different values,
> leading to maintenance issues and bugs down the track.
>=20
> On the other hand, imposing an arbitrary limitation, based on little
> more than a guess by the designer, can cause enormous problems. As an
> example, I was working on an identity management system where there =
was
> a constraint of 8 characters on the username and password. This was an
> arbitrary limit based on what was common practice, but was not a
> limitation imposed by any of the systems the IAM system interacted =
with.
> It was recognised that both fields were too small and needed to be
> increased. The easy solution would have been to make these fields =
text.
> However, that would cause a problem with some of the systems we needed
> to integrate with because either they had a limit on username size or
> they had a limit on password size. There were also multiple different
> APIs which needed to work with this system and when we performed
> analysis, they had varying limits on both fields.
>=20
> What we did was look at all the systems we had to integrate with and
> found the maximum supported username and password lengths for each
> system and set the fields to have the maximum length supported by the
> systems with the shortest lengths. Having that information in the
> database schema also informed those developing other interfaces what =
the
> maximums were. It is quite likely these limits would be increased in =
the
> future and the database definition would need to be increased - in =
fact,
> some years after going into production, exactly this occurred with the
> password field when a different encryption algorithm was adopted which
> did not have the previous character limitation and the client wanted =
to
> encourage users to use pass phrases rather than a word.=20
>=20
> The point is, just using text for all character fields loses =
information
> and results in your model and schema being less expressive. Providing
> this information is sometimes critical in ensuring limits are =
maintained
> and provides useful documentation about the model that developers can
> use. However, imposing limits based on little more than a guess is
> usually a bad idea and if you cannot find any reason to impose a =
limit,
> then don't. I disagree with approaches which claim using text =
everywhere
> is easier and future proofing. In reality, it is just pushing the
> problem out for someone else to deal with. The best way to future =
proof
> your application is to have a clear well defined data model that fits
> the domain and is well documented and reflected in your database =
schema.=20
>=20
>=20
> --=20
> Tim Cross
>=20

I can=E2=80=99t agree more=E2=80=A6 Thanks Tim.