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.
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 |
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 |