CODEWITHSUNDEEP
mysql
c0dehunter
c0dehunter

Reputation: 6150

MySql: are nested tables possible?

How can I design a table to hold the following data:

name
age
last_30_measurements
    timestamp, value
    timestamp, value
    timestamp, value
    timestamp, value
    ..

As you can see, last_30_measurements could be its own table, containing a list of pairs timestamp,value.

What is the proper way to do this?

Upvotes: 0

Views: 218

Answers (1)

Cid
Cid

Reputation: 15247

I would use another table for that purpose and use a foreign keys to link both.

In example :

Schema (MySQL v5.7)

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

CREATE TABLE mesurements
(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    mesure_date DATETIME NOT NULL,
    mesure_value INT NOT NULL,
    mesurer_dude_id INT NOT NULL,
    CONSTRAINT FOREIGN KEY (mesurer_dude_id) REFERENCES mesurer_dude(id)
);

INSERT INTO mesurer_dude VALUES (default, "John", 42), (default, "Jane", 69);
INSERT INTO mesurements VALUES (default, NOW(), 10, 1), (default, DATE_ADD(NOW(), INTERVAL 1 DAY), 20, 1), (default, DATE_ADD(NOW(), INTERVAL 2 DAY), 25, 1), (default, DATE_ADD(NOW(), INTERVAL 3 DAY), 15, 1), (default, DATE_ADD(NOW(), INTERVAL 4 DAY), 12, 2);

Query #1

-- get the last 30 mesurements of john
SELECT *
FROM mesurer_dude d
INNER JOIN mesurements m
ON d.id=m.mesurer_dude_id
WHERE d.name = 'John'
ORDER BY m.mesure_date DESC
LIMIT 30;

Result : 

| id  | name | age | id  | mesure_date         | mesure_value | mesurer_dude_id |
| --- | ---- | --- | --- | ------------------- | ------------ | --------------- |
| 1   | John | 42  | 4   | 2018-11-25 14:37:33 | 15           | 1               |
| 1   | John | 42  | 3   | 2018-11-24 14:37:33 | 25           | 1               |
| 1   | John | 42  | 2   | 2018-11-23 14:37:33 | 20           | 1               |
| 1   | John | 42  | 1   | 2018-11-22 14:37:33 | 10           | 1               |

View on DB Fiddle

Upvotes: 5

Related Questions