Am I breaking 2NF rule for using array data type in PostgresSQL?

Question

I have a Users table with cars ownership represented as array of integers (foreign keys). Personally, it is much easier to use array data type instead of normalizing this table to 2NF as in my business logic I can iterate/fetch each car separately by querying Cars table whenever needed. However, my focus here is not on the implementation details but on the best practices and potential pitfalls of using array data types in RDBMs systems.

Users table
╔════════╦══════╦═════════╗
║ userId ║ name ║ carsOwn ║
╠════════╬══════╬═════════╣
║    1   ║ Jon  ║ {2}     ║
║    2   ║ Yang ║ {1,3,4} ║
║    3   ║ Ali  ║ {1,4}   ║
║    4   ║ Kate ║ {1,2}   ║
╚════════╩══════╩═════════╝

Cars table
╔═══════╦══════════╦
║ carId ║ name     ║
╠═══════╬══════════╬
║   1   ║ honda    ║
║   2   ║ toyota   ║
║   3   ║ smart-car║
║   4   ║ bentley  ║
╚═══════╩══════════╩

1NF (First Normal Form)
Each table cell should contain a single value.
Each record needs to be unique.
2NF (Second Normal Form) Rules
Be in 1NF
Single Column Primary Key

Yes, that breaks 1NF and in most cases a join to a properly normalized many-to-many relationship will be just as fast (if not faster) than using a join over the array — , Nov 19 '19 at 21:44
I'm not opposed to using arrays when it makes sense to do so, but in this case your join relies on the values stored in the array. This feels like a *VERY* bad design decision. — JNevill, Nov 19 '19 at 21:44
Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Include a legend/key & explanation with an image. — philipxy, Nov 20 '19 at 00:48
["1NF" has no 1 meaning.](https://stackoverflow.com/a/40640962/3404097) Higher NFs address problem FDs & JDs regardless of "1NF". Generally for good design, don't make columns of "composite" types--those that contain other types that you use for columns; make relation the only such "composite" type. Your "1NF" is just "is a relation". Your "2NF" is not 2NF. This question is both too broad & unresearched. See [ask], hits googling 'stackexchange homework' & the voting arrow mouseover texts. Time to follow a published academic textbook on information modelling, the relational model & DB design. — philipxy, Nov 20 '19 at 01:26
This shouldn’t be closed as it asks about foreign keys in the array whilst the other question is about storing an array of values. — vaughan, Apr 18 '21 at 20:39

score 3 · Accepted Answer · answered Nov 19 '19 at 22:32

The important normal form here is the first one.

The SQL standard 1999 has somewhat widened the perspective here by introducing compound data types. The best way to look at it is that a datum is atomic if it is atomic in the context of the data model. Otherwise you'd have to consider each string non-atomic (it consists of characters!) and split it up.

So I'd say that there is nothing wrong in using arrays as long as you don't need to fetch individual array items in the database or — horribile dictu — perform joins with array items.

Now this is exactly what you want to do, so you should definitely normalize the data model. Your queries will be much simpler and probably faster too.

Am I breaking 2NF rule for using array data type in PostgresSQL?

1 Answers1