Groups | Search | Server Info | Keyboard shortcuts | Login | Register

Groups > comp.databases.postgresql > #889

Re: Table with a variable number of elements in a column

Show all headers | View raw

alexander.d.mills@gmail.com writes:

> I have a similar question/answer on StackExchange: 
> https://softwareengineering.stackexchange.com/questions/405567/
> how-to-use-strict-schema-with-seemingly-fluid-data-type

I have seen that kind of modelling elsewhere.  This is basically
an EAV (Entity Attribute Value) table?  The book [1,2] describes
it as an anti-pattern which should be avoided because it is
harder to use, control and query later.

Example: The email should probably satisfy some constraints but
it is of type "json" and anything can be stored in it.  How about
typos in the "key" field etc.  Note that processing may consume
more CPU time as well according to Wikipedia [3] - depending on
the planned size of your database.

So, before using this pattern, I would think about whether it is
really necessary to have that flexibility (at the cost of the
disadvantages) or if it is possible to use normalization theory
and create relations for the entities.

| is there a better way to do this other than using JSON for the
| value column?

With the example shown, why are you using a "JSON" field instead
of a text field?  If you are using a JSON field, I would store
all emails in one JSON array field and make (user_id, key)
unique:

| id | user_id | key   | value                                |
|----|---------|-------|--------------------------------------|
| 1  | 1       | email | ["foo1@bar.com", "foo2@bar.com", ..] |
...

Why are there more than one email addresses for each user?  Do
they serve a specific purpose?  With your design, it seems
impossible to select some email addresses for certain actions
(which may be OK?).

An alternative version might be:  use a N:M mapping between
"user" and "email" (the "email" table contains a list of email
addresses a user may specify, i.e. "email at work", "private
mail", etc. and link the user with zero or more email
addresses).

(Further N:M mappings might be necessary for other "key"
values which might lead to a larger number of tables but column
constraints and queries would be simpler)

| If not - is there a way to enforce a schema on the JSON
| somehow?

You can do this with a trigger that runs before the insertion.
You have to carefully check the layout of the JSON and the
constraint of its values (i.e. if the key=email, then the value
field should have a certain pattern etc.).  I would also check
the "key" field and only allow existing keys (so that it becomes
possible to add "e-mail", "E-Mail", ... ).  PostgreSQL comes with
nice functions and operators to work with JSON [4].

| Last question - from my brief research the inverse table design
| is called an "unpivot" table - but if there is a  better name
| for it please let me know. 

"unpivot" sounds a bit "unspecific".  Personally, I'd name it
"user_meta_data" or "user_contacts" (if there are only email
addresses).

[1] D. Fontaine: Mastering PostgreSQL In Application Development
[2] https://tapoueh.org/blog/2018/03/database-modelization-anti-patterns/#entity-attribute-values
[3] https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model
[4] https://www.postgresql.org/docs/11/functions-json.html
-- 
Christian Barthel <bch@online.de>

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Find similar

Thread

Table with a variable number of elements in a column Bill Gunshannon <bill.gunshannon@gmail.com> - 2019-04-27 14:59 -0400
  Re: Table with a variable number of elements in a column John-Paul Stewart <jpstewart@sympatico.ca> - 2019-04-27 15:39 -0400
    Re: Table with a variable number of elements in a column Bill Gunshannon <bill.gunshannon@gmail.com> - 2019-04-27 15:48 -0400
      Re: Table with a variable number of elements in a column John-Paul Stewart <jpstewart@sympatico.ca> - 2019-04-27 16:02 -0400
        Re: Table with a variable number of elements in a column Bill Gunshannon <bill.gunshannon@gmail.com> - 2019-04-27 16:29 -0400
          Re: Table with a variable number of elements in a column John-Paul Stewart <jpstewart@sympatico.ca> - 2019-04-27 17:10 -0400
            Re: Table with a variable number of elements in a column Bill Gunshannon <bill.gunshannon@gmail.com> - 2019-04-27 18:40 -0400
              Re: Table with a variable number of elements in a column John-Paul Stewart <jpstewart@sympatico.ca> - 2019-04-27 19:26 -0400
  Re: Table with a variable number of elements in a column alexander.d.mills@gmail.com - 2020-02-21 14:02 -0800
    Re: Table with a variable number of elements in a column Christian Barthel <bch@online.de> - 2020-02-22 09:15 +0100

csiph-web