When a record can have a variable number of values of the same kind, store each value in its own row in a related table—not in a comma-separated cell or a set of numbered columns. For favorite fruits, keep user details in users and each selection in a user_fruit table. Use separate columns for distinct attributes, and a single column on the parent record when exactly one value is allowed.
Choose columns for distinct attributes and rows for repeating values
Columns describe different facts about one record: a user’s first name, email address, and phone number belong in separate columns because each has a distinct meaning. A changing list of favorite fruits is different: it is one kind of fact that may occur zero, one, or many times for a user.
Adding columns such as fruit_1, fruit_2, and fruit_3 builds a fixed limit into the schema. A comma-separated value such as apple,pear,plum hides several values inside one field. In a relational design, represent a variable-length set as related rows instead. A user with five selections has five rows; a user with none has none.
When a fixed set of columns is reasonable
Use separate columns when the fields have distinct, stable meanings, such as first, middle, and last name. A truly fixed set of values may also fit columns—for example, four quarter scores when the domain and queries are specifically defined around those four quarters. If the number can grow, as with overtime periods or an expanding list of preferences, rows avoid schema changes for each new instance. A database-administration example models game periods as rows so overtime can be represented without adding columns: DBA Stack Exchange: Design: Multiple Values in One Column or Many Columns.
#1 Best Overall
When exactly one value is allowed
If every user may select exactly one favorite fruit, a favorite_fruit_id column on users can express that relationship. A separate child table becomes useful when the rule changes to allow multiple selections, or when each selection needs its own attributes.
A schema for users and multiple favorite fruits
When fruit choices come from a controlled list, use one table for users, one for fruits, and a junction table connecting them:
CREATE TABLE users (
user_id bigint PRIMARY KEY,
name text NOT NULL,
phone_number text,
email_address text
);
CREATE TABLE fruit (
fruit_id bigint PRIMARY KEY,
name text NOT NULL UNIQUE
);
CREATE TABLE user_fruit (
user_id bigint NOT NULL REFERENCES users(user_id),
fruit_id bigint NOT NULL REFERENCES fruit(fruit_id),
PRIMARY KEY (user_id, fruit_id)
);Each row in user_fruit represents one user–fruit pairing. The foreign keys prevent a pairing from referring to a nonexistent user or fruit, while the composite primary key prevents the same fruit from being selected twice by the same user. PostgreSQL explains how foreign-key constraints preserve valid references in its constraints documentation.
The numeric IDs here are illustrative, not mandatory. A natural key can be appropriate if it is stable, unique, and practical to use. PostgreSQL’s tutorial, for example, demonstrates a text city name as a primary key and foreign-key target: PostgreSQL foreign-key tutorial.
Free tools Windows power users keep installed
One-click scans. No signup required.
When to add a lookup table
A fruit lookup table is useful when the application needs a controlled vocabulary, a stable reference for a form, or metadata about each fruit. It is not required merely because a value appears more than once. If values are free-form and do not need shared metadata, the relationship design can be simpler; the key requirement is still to represent each independently searchable selection as its own row.
When selections have order or other properties
If users rank preferences or selections have dates or other attributes, store those facts on the relationship row—for example, preference_order or added_at. Then decide what must be unique: if a user can select a fruit only once, retain uniqueness on the user–fruit pair; if repeated selections are meaningful events, give each event its own key and define the appropriate constraints.
Arrays and delimited strings: alternatives with trade-offs
Some database systems support array-valued columns, but arrays change how values are queried and constrained. PostgreSQL’s version 18 documentation states: “Arrays are not sets; searching for specific array elements can be a sign of database misdesign.” It recommends considering a separate row for each element, which can make searching easier and may scale better for many elements. This advice is specifically about PostgreSQL arrays; other systems have different features and trade-offs. See the PostgreSQL arrays documentation.
A delimited string is especially awkward when the application needs to find users who like apples, join selections to fruit details, validate entries, update one choice, or report on individual values. The application then has to split and interpret the string, including handling delimiters and escaping. The available sources do not compare every database’s string, JSON, or array implementation, so the practical choice depends on the target database and the operations the application needs.
Querying and indexing the relationship
With a row per selection, a query for users who like apples can join through the fruit table rather than parse a packed field. The composite primary key in the example begins with user_id, which supports looking up a user’s choices. Searching in the opposite direction—finding users by a particular fruit—may benefit from a separate index beginning with fruit_id.
Choose indexes based on actual access paths and workload. In PostgreSQL, declaring a foreign key does not automatically create an index on the referencing columns; its documentation notes that such indexes can be useful. The same documentation covers foreign keys and constraints: PostgreSQL constraints.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Postal codes are identifiers, not quantities
Store ZIP and postal codes as text rather than numbers: leading zeroes can be significant, and arithmetic on a postal code is not meaningful. Whether to maintain a postal-code lookup table depends on whether the application needs standardized geographic data and whether the chosen dataset has suitable quality, licensing, and update practices. Do not assume that every postal code maps one-to-one to a city across all geographies and datasets.
Does millions of relationship rows mean you need partitioning?
No universal row-count threshold follows from the example. The five-million-row figure raised in the original SitePoint discussion is hypothetical, not a benchmark or a demonstrated partitioning cutoff. Whether partitioning helps depends on the database, query patterns, write rate, row width, hardware, and operational goals. Start by measuring the real workload, reviewing query plans, and choosing useful indexes; consider partitioning only when evidence from that workload supports it. The original discussion is available at SitePoint Forums: Multiple Values in One Column or Many Columns?.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Quick Recap
A practical decision checklist
- Use separate columns for distinct attributes with stable meanings.
- Use one column on the parent record if exactly one value is allowed.
- Use one related row per value when the count can vary or values must be searched and managed individually.
- Use a lookup table when it enforces a controlled vocabulary or provides useful shared metadata—not simply because values repeat.
- Define keys and uniqueness rules to match whether duplicate selections are valid.
- Add indexes for the queries the application actually runs, and assess performance from workload evidence rather than row count alone.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
