Having a main column and two sub-columns in SQL Server tables

Question

I have a small query with respect to the creation of tables in SQL Server.

I have 6 columns in the table. Each of the columns has a name.

In each of these columns, I want to further divide it into two columns named Positive and Negative.

Therefore

• Column1 (Positive | Negative)
• Column2(Positive | Negative)...

would be table schema.

I understand that creating the columns and then associating the names to them would be easy, however how can I divide the column into two while creating the table and name them as sub-columns.

Answer 1

If Column1_Positive and Column1_Negative are two distinct attributes, then you need two individual columns to store those attributes. There is no subdividing a single column into two elements.

Answer 2

As per Joe Stefanelli's answer, you can't sub-divide a column.

It feels as though you're trying to treat each column as a struct, or a class, of it's own. You're better off thinking of a TABLE as a struct, and each FIELD as a primitive.

the closest you can get to what you are asking is to either create 12 columns, and name them appropriately (C1_Pos, C1_Neg, C2_Pos, C2_Neg, etc) or to create two tables.

One table would have it's primary key column(s) and then two value columns; positive and negative. Each entry in the main table would then refer to this table.

For example...

CREATE TABLE value_pair (
  id                 INT,
  positive           INT,
  negative           INT,
  PRIMARY KEY (id)
);

CREATE TABLE master (
  id                 INT,
  value_pair_id_1    INT,
  value_pair_id_2    INT,
  value_pair_id_3    INT,
  value_pair_id_4    INT,
  value_pair_id_5    INT,
  value_pair_id_6    INT,
  PRIMARY KEY (id),
  FOREIGN KEY (value_pair_id_1) REFERENCES value_pair (id),
  FOREIGN KEY (value_pair_id_2) REFERENCES value_pair (id),
  FOREIGN KEY (value_pair_id_3) REFERENCES value_pair (id),
  FOREIGN KEY (value_pair_id_4) REFERENCES value_pair (id),
  FOREIGN KEY (value_pair_id_5) REFERENCES value_pair (id),
  FOREIGN KEY (value_pair_id_6) REFERENCES value_pair (id)
);

Then, however, you're forced to join the two tables whenever you want to grab one of the values. And if you want ALL the values, you have to join 6 times...

SELECT
  *
FROM
  master
INNER JOIN
  value_pair    AS [VP1]
    ON master.value_pair_id_1 = [VP1].id
INNER JOIN
  value_pair    AS [VP2]
    ON master.value_pair_id_2 = [VP1].id
INNER JOIN
  value_pair    AS [VP3]
    ON master.value_pair_id_3 = [VP1].id
INNER JOIN
  value_pair    AS [VP4]
    ON master.value_pair_id_4 = [VP1].id
INNER JOIN
  value_pair    AS [VP5]
    ON master.value_pair_id_5 = [VP1].id
INNER JOIN
  value_pair    AS [VP6]
    ON master.value_pair_id_6 = [VP1].id

Personally, I'd just use 12 columns...