How to structure (normalize?) a database of physical parameters?
- by Arrieta
Hello:
I have a collection of physical parameters associated with different items. For example:
Item, p1, p2, p3
a, 1, 2, 3
b, 4, 5, 6
[...]
where px stands for parameter x.
I could go ahead and store the database exactly as presented; the schema would be
CREATE TABLE t1 (item TEXT PRIMARY KEY, p1 FLOAT, p2 FLOAT, p3 FLOAT);
I could retrieve the parameter p1 for all the items with the statement:
SELECT p1 FROM t1;
A second alternative is to have an schema like:
CREATE TABLE t1 (id INT PRIMARY KEY, item TEXT, par TEXT, val FLOAT)
This seems much simpler if you have many parameters (as I do). However, the parameter retrieval seems very awkward:
SELECT val FROM t1 WHERE par == 'p1'
What do you advice? Should go for the "pivoted" (first) version or the id, par, val (second) version?
Many thanks.
