SQL Database Design » 14. Normalization » 14.1 Repeating Attributes

14.1 Repeating Attributes

Repeating attributes are properties like "email1", "email2", ... or "child1", "age1", "child2", "age2", ... or "primary_color", "secondary_color", ... etc. It basically affects all columns that you are numbering in some way. Repeating attributes frequently occurr in bad designs and indicate improper misconception of distinct entity types.

Collective types are an automatic way of repeating attributes. Technically, collective types are just as bad as repeating attributes. Both lead to the very same problems, but structured types are no better. In a way, you can abuse structured types to repeat attributes like "phone1", "phone2", ... as well. Take the example table from the 10.3 Object-Relational Types section:

CREATE VALUE TYPE address AS
(
  street VARCHAR(50),
  addon  VARCHAR(50),
  zip    CHAR(5),
  city   VARCHAR(50)
)

CREATE TABLE Students
(
  id              INTEGER PRIMARY KEY,
  last_name       VARCHAR(30),
  first_name      VARCHAR(20),
  home_address    address,
  branch_of_study VARCHAR(30) NOT NULL,
)

Some people consider the above table to be just another form of inlining the attributes, like:

CREATE TABLE Students
(
  id              INTEGER PRIMARY KEY,
  last_name       VARCHAR(30),
  first_name      VARCHAR(20),
  address_street  VARCHAR(50),
  address_addon   VARCHAR(50),
  address_zip     CHAR(5),
  address_city    VARCHAR(50),
  branch_of_study VARCHAR(30) NOT NULL,
)

Here, the attributes of another entity type is inlined into the table. This is the same argument as for collective types and repeating attributes in general. The only difference to collective types here is, that the above is not a one-to-many and not a one-to-one relationship, which is just a "mechanical" issue.

Revisiting the above table, if a student can be immatriculated in more than one branch of study, you could declare the students table with a collective type:

CREATE TABLE Students
(
  ...
  branch_of_study VARCHAR(30) NOT NULL MULTISET,
  ...
)

You could even combine collective and structured types by declaring sets of structured types, e.g. when allowing a student to have more than one address:

CREATE TABLE Students
(
  ...
  addresses address NOT NULL MULTISET, -- at least one address
  ...
)

As seen in the above examples, there is nothing that prevents you from repeating attributes, neither as explicit columns, nor as collective or structured types. You also cannot prevent encoding multiple values into one column, e.g. gregorian dates, which have year, month, and day info.

Last updated: 2010-08-04