0

I am new to databases and now i came to a point where i need more complex table. I have a table which contains site details. Each site can have multiple devices. Each device have multiple measurement tables.

Structure looks like this:

                    DEVICE LIST TABLE 1 ► MEASUREMENT TABLE (each device)
SITE LIST TABLE ►   DEVICE LIST TABLE 2 ► MEASUREMENT TABLE (each device)
                    DEVICE LIST TABLE 3 ► MEASUREMENT TABLE (each device)

Measurement table looks like this

+----+----+----+----+----+
|    | F1 | t1 | F2 | t2 |
+----+----+----+----+----+
| A1 |    |    |    |    |
+----+----+----+----+----+
| A2 |    |    |    |    |
+----+----+----+----+----+
| A3 |    |    |    |    |
+----+----+----+----+----+
| A4 |    |    |    |    |
+----+----+----+----+----+
| A5 |    |    |    |    |
+----+----+----+----+----+
| A6 |    |    |    |    |
+----+----+----+----+----+

I do not understand how to make table like this. If not possible what is the best practice to link every ROW with device.

Or should i create dynamically measrements A1 to An?

+-----------+---------+----+----+----+----+
| device_id | name    | F1 | t1 | F2 | t2 |
+-----------+---------+----+----+----+----+
|           | A1...An |    |    |    |    |
+-----------+---------+----+----+----+----+

And then do

SELECT * FROM measurement_table WHERE device_id = x

1 Answer 1

2

You should revise your table structure. You are trying to put too many different things into one table.

What you could do is have multiple tables like so:

Table: site Has an ID and all other details about the site! Table: Devices Has an ID and all other details about the device. Table: measurements Has an ID and all other details about the measurement.

Now depending on if your measurements are linked to a single device or not you may need an intersection table or a device ID on the measurement, and the same goes for the site.

When you wish to select all the details about a site, you will need to use joins to get everything, or maybe multiple queries.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.