Ok. We have a bunch of different components in our system that write about a particular investment. These components know nothing about each other, except the item they are operating on.
Let's take 3 components: ComponentA, ComponentB, ComponentC -- all of these write a "FLAT FILE"! We then parse each file in parallel line-by-line. Each component can choose to write whatever data it wants to (in fact they do, each component is a different team in my organization). But we've all agreed on an "OperationID", which happens to be a 128-bit unique value. Each component can write "MULTIPLE" rows of information and I don't know how many rows it can write, but they will all be of the same schema. ComponentA can choose to write: FooId, SomeStringValue, SomeIntValue (and it can write many rows) ComponentB can choose to write: FooId, SomeOtherColumn (and it can write many rows) ComponentC can choose to write: FooId, WhateverColumnNameItNeedsTo (and it can write many rows) --> End Goal: A SQL Table: FooId,SomeStringValue,SomeIntValue,SomeOtherColumn,WhateverColumnNameItNeedsTo, .... FooId2,..... So is what RSmith is suggesting what I really want? I've spent so many hours on this.I'm actually clueless now as well. On Thu, Dec 5, 2013 at 11:45 AM, RSmith <rsm...@rsweb.co.za> wrote: > > Apologies, my mail is slow today, did not notice this thread had > progressed significantly before I posted - please ignore previous. > > I'm with Igor though, the multi-table layout you now have is even less > convenient than the matrix - It's equally dispersed data only now you have > to join 3 tables for the result. Any chance you could share with us the > exact thing you are trying to store and the exact resulting knowledge you > wish to deduce from the stored data? Maybe we can come up with more > helpful suggestions (as opposed to just looking puzzled!). > > Cheers, > Ryan > > > On 2013/12/05 20:54, Igor Tandetnik wrote: > >> On 12/5/2013 1:43 PM, Hayden Livingston wrote: >> >>> Yes, are moving our sparse matrix to different tables: >>> >>> Id | RelationalIdentifier | ColA >>> 1 a X >>> 2 b A >>> >>> Id | RelationalIdentifier | ColB >>> 1 a Y >>> 2 b B >>> >>> Id | RelationalIdentifier | ColC >>> 1 a Z >>> 2 b C >>> >> >> Why? Personally, I'd have one table: either >> >> Id, RelationalIdentifier, ColA, ColB, ColC >> 1, a, X, Y, Z >> 2, b, A, B, C >> >> or >> >> Id, RelationalIdentifier, Col, Value >> 1, a, ColA, X >> 1, a, ColB, Y >> 1, a, ColC, Z >> 2, b, ColA, A >> 2, b, ColB, B >> 2, b, ColC, C >> >> depending on how open-ended the list of columns is. It's also not clear >> what the purpose of Id and RelationalIdentifier is; they appear duplicative >> of each other. >> >> How can I adapt your query to this new table schema? >>> >> >> select RelationalIdentifier, ColA, ColB, ColC >> from TableA join TableB using (RelationalIdentifier) join TableC using >> (RelationalIdentifier); >> >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
