The I suggest normalizing the schema. Table genes (gene_id integer primary key, name char)
Table variant (variant_id integer primary key, gene_id integer, name char) Table sample (sample_id integer primary key, name char, ...); Table sample_gene_variant (sample_id integer, gene_id integer, variant_id integer, unique (sample_id, gene_id)); Appropriate foreign keys are recommended. Then you can store your 100k bits of information per sample in the sample_gene_variant relation. Also, should the need arise to store new genes or variants, all it takes is new entries in the gene or variant tables, instead of adding more columns to an already bloated table. Getting the value of a specific column from the intiial schema is then a simple join: SELECT s.name, g.name, v.name from sample s join gene g join sample_gene_variant sgv on (sgv.sample_id = s.sample_id and sgv.gene_id = gene.gene_id) join variant v on (v.variant_id = sgv.variant_id) where s.name = 'John Doe' and g.name = 'BCL11B'; -----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Mitar Gesendet: Mittwoch, 16. Oktober 2019 20:03 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Limit on number of columns in SQLite table Hi! On Wed, Oct 16, 2019 at 3:16 PM Hick Gunter <h...@scigames.at> wrote: > 100k distinct column names? Or is that 10000 repeats of 10 attributes? 100k distinct names. Like each column a different gene expression. Mitar -- http://mitar.tnode.com/ https://twitter.com/mitar_m _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
