On Fri, Apr 30, 2010 at 07:57:05AM -0700, David Lyon wrote:
> if I had many many files like this:
> http://research.stowers-institute.org/efg/ScientificSoftware/Utility/FCSExtract/CC4_067_BM.txt
>
> you see 2 columns keyword and value, the keywords would be the fields
> (1st column in the html link above) in the table while the values
> (second column) are the data I would insert.
When you get to have many similar columns in one table you should
consider normalizing. So a table with 10 sets of 6 very similar
columns, as in your example:
CREATE TABLE project (name TEXT, ...,
P1R INT, P1B INT, P1N TEXT, P1S TEXT, P1E TEXT, P1P INT,
P2R ...);
can become two tables:
CREATE TABLE project (project_id INTEGER PRIMARY KEY, name TEXT, ...);
CREATE TABLE projectPs (project_id INT, x INT,
PxR INT, PxB INT, PxN TEXT, PxS TEXT, PxE TEXT,
PxP INT);
-- get P1R, P2R and P3R for name = ...
SELECT proj.*, ps.PxR FROM project proj NATURAL JOIN projectPs ps WHERE
proj.name = ... AND x < 4;
or:
CREATE TABLE project (project_id INTEGER PRIMARY KEY, name TEXT, ...);
CREATE TABLE project_fields (project_id INT, keyword TEXT, value TEX);
-- get P*R for name = ...
SELECT proj.*, pf.value FROM project proj NATURAL JOIN project_fields pf
WHERE proj.name = ... AND pf.keyword LIKE P_R;
or:
CREATE TABLE project (project_id INTEGER PRIMARY KEY, name TEXT, ...);
CREATE TABLE project_int_fields (project_id INT, keyword TEXT, value INT);
CREATE TABLE project_text_fields (project_id INT, keyword TEXT, value TEXT);
and so on.
You could go all the way and just have one table indexed by file name
and keyword with a value column. However, understanding your data will
help you do better; the file you point to doesn't really include enough
information to know how best to represent it and others like it in a SQL
schema.
Nico
--
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
