I am just learning to use relational databases, so my knowledge is limited. I have data points, that rely on 3 values: Product type, age and a value x. So for each product type, there is a matrix from which one can get the price dependent on age and value x. Product A Value x Age 0 - 18 19 - 64 65 - 150 50 5.6 6.3 3.5 100 5.2 3.5 6.3 200 6.4 3.7 12.3 500 3.9 2.3 5.5 There are several of those products. The values of x and the ranges of age can vary. So the price of the product is dependend on product type, age, and value x. How do I want to design my tables in order to provide normalization? Thank you in advance.

How to handle multi dimensional matrices in SQL?

Reputation: 82524

Basically, you would have 4 tables.

One will contain the product information,
One will contain the age ranges (note: This assumes age ranges are the same for each product)
One will contain X values,
and the last one will contain the price, using foreign keys to 3 tables I've listed before.

Since you have different age ranges and x values for each product, these tables should also have foreign keys referencing the products table.

Something like this should get you started:

create table products 
(
    id int primary key,
    name varchar(100)
    -- other product related details such as description ans stuff
);

create table ageRanges
(
    product_id int foreign key references products(id),
    id int primary key,
    name varchar(100)
)

create table X
(
    product_id int foreign key references products(id),
    value int
)

create table prices
(
    product_id int foreign key references products(id),
    ageRange_id int foreign key references ageRanges(id), 
    x_id int foreign key references X(id), 
    price numeric(10, 2)
)

Upvotes: 1

Cid

Reputation: 15247

You may want 4 tables.

1 table storing the min and max age (each range in fact)

1 table storing the value x

1 table storing the product name (and some other informations)

1 relation table between the tables age, value x and product name, storing the foreign keys of them plus the price. the primary key of this table is the composition of all FK to ensure unique datas.

I'm actually writting SQL code to provide you examples.

Schema (MySQL v5.7)

CREATE TABLE age
(
  id INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  min_age INT(6) NOT NULL,
  max_age INT(6) NOT NULL
);

CREATE TABLE valuex
(
  id INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  val INT(6) NOT NULL
);

CREATE TABLE products
(
  id INT(6) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE pricing
(
  age_id INT(6) NOT NULL,
  valuex_id INT(6) NOT NULL,
  products_id INT(6) NOT NULL,
  price DECIMAL(5, 2) NOT NULL,
  FOREIGN KEY (age_id) REFERENCES age(id),
  FOREIGN KEY (valuex_id) REFERENCES valuex(id),
  FOREIGN KEY (products_id) REFERENCES products(id),
  PRIMARY KEY (age_id, valuex_id, products_id)
);

INSERT INTO age VALUES (default, 0, 18), (default, 19, 64 ), (default, 65, 150);
INSERT INTO valuex VALUES (default, 5), (default, 100), (default, 200), (default, 500);
INSERT INTO products VALUES (default, "A");

INSERT INTO pricing VALUES (1, 1, 1, 5.6),
                           (2, 2, 1, 6.3),
                           (3, 3, 1, 3.5),
                           (1, 4, 1, 5.2),
                           (2, 1, 1, 3.5),
                           (3, 2, 1, 6.3),
                           (1, 3, 1, 6.4),
                           (2, 4, 1, 3.7),
                           (3, 1, 1, 12.3),
                           (1, 2, 1, 3.9),
                           (2, 3, 1, 2.3),
                           (3, 4, 1, 5.5);

Query #1

-- Get all prices for product A
SELECT CONCAT(a.min_age, ' - ',a.max_age) AS "Age range",
       v.val AS "Value x",
       pr.name AS "Product Name",
       p.price AS "Price"
FROM pricing p
LEFT JOIN age a
ON a.id = p.age_id
LEFT JOIN valuex v
ON v.id = p.valuex_id
LEFT JOIN products pr
ON pr.id = p.products_id
WHERE pr.name = "A";

Output

| Age range | Value x | Product Name | Price |
| --------- | ------- | ------------ | ----- |
| 0 - 18    | 5       | A            | 5.6   |
| 0 - 18    | 100     | A            | 3.9   |
| 0 - 18    | 200     | A            | 6.4   |
| 0 - 18    | 500     | A            | 5.2   |
| 19 - 64   | 5       | A            | 3.5   |
| 19 - 64   | 100     | A            | 6.3   |
| 19 - 64   | 200     | A            | 2.3   |
| 19 - 64   | 500     | A            | 3.7   |
| 65 - 150  | 5       | A            | 12.3  |
| 65 - 150  | 100     | A            | 6.3   |
| 65 - 150  | 200     | A            | 3.5   |
| 65 - 150  | 500     | A            | 5.5   |

Listing of all tables content

Query #2

SELECT * FROM age;

| id  | min_age | max_age |
| --- | ------- | ------- |
| 1   | 0       | 18      |
| 2   | 19      | 64      |
| 3   | 65      | 150     |

Query #3

SELECT * FROM valuex;

| id  | val |
| --- | --- |
| 1   | 5   |
| 2   | 100 |
| 3   | 200 |
| 4   | 500 |

Query #4

SELECT * FROM products;

| id  | name |
| --- | ---- |
| 1   | A    |

Query #5

SELECT * FROM pricing;

| age_id | valuex_id | products_id | price |
| ------ | --------- | ----------- | ----- |
| 1      | 1         | 1           | 5.6   |
| 1      | 2         | 1           | 3.9   |
| 1      | 3         | 1           | 6.4   |
| 1      | 4         | 1           | 5.2   |
| 2      | 1         | 1           | 3.5   |
| 2      | 2         | 1           | 6.3   |
| 2      | 3         | 1           | 2.3   |
| 2      | 4         | 1           | 3.7   |
| 3      | 1         | 1           | 12.3  |
| 3      | 2         | 1           | 6.3   |
| 3      | 3         | 1           | 3.5   |
| 3      | 4         | 1           | 5.5   |

View on DB Fiddle

Upvotes: 2

How to handle multi dimensional matrices in SQL?

Answers (3)

Listing of all tables content

Related Questions