On Thursday, 16 July, 2015, 21:50, Hayden Livingston <halivingston at gmail.com> said:
> Thanks, Keith for correcting my usage. > I'm referring to tables when I mean database schema, so pardon my > technical terminology. > I have multiple tables of data already, say TableA, TableB. These > tables have different representations, one may contain a TEXT column, > the other may contain and INTEGER column, but all of them contain an > INTEGER field called time, which is unique. Ok, I understand your usage now. Question: Do you have multiple data items (ie, for the different tables data column) that have the same primary key (ie, the TIME)? Do you need to be able to query against the "data" value(s)? Do you need to "match" the results with the data column name? > I also want to be able to query the union of results TableA and > TableB. This is more of an architecture question to get the best > performance. Given the requirement to also show the data in a format > that is less schematic and more free-form how do people achieve that. > I thought of the case that I could double write my data into TableA > and TableB, and then also "CommonTable", which is the union of all > entries in TableA, and TableB. Of course since there column number and > types may not match, I create a "representation" -- basically take all > the columns of that entry and append them into a single string. > so my CommonTable looks like: > CREATE TABLE CommonTable (INTEGER TimeEntry, TEXT COMMONDATA) > This seems wasteful in terms of space -- I was wondering if others had > thoughts. One possible thought is that you can indeed put multiple data items in the same table, and you can store any "type" of data in any location. If all you need is say four values, and a time you could use something like: create table DataTable(Time Integer Primary Key, Value1 numeric default null, value2 numeric default null, value3 numeric default null); Then you store the data in whatever format it is in the table, setting the columns that do not apply to null. You can then issue your query to output your commondata string: select time, coalesce(Value1, '') || ', ' || coalesce(Value2, '') || ', ' || 'coalesce(Value3, '') from DataTable where Time between 159 and 2000 and 25 in (Value1, Value2, Value3) and 'Test Data' in (Value1, Value2, Value3) for example. Efficiency will be based on knowing the right column to look in for constraints other than the Time (which will determine the number of rows to scan). > On Thu, Jul 16, 2015 at 7:55 PM, Keith Medcalf <kmedcalf at dessus.com> > wrote: > >> I have different schema data that I'd like to store across tables, and > >> it is sorted in chronological order. > > > > Please explain what you mean by "schema", and especially "schema data". > In a relational database there is only one schema per database. This > single schema describes the tables and indices, views, and triggers stored > in that particular database (and, for other database engines, there may be > other types of objects described in the schema). > > > > Some database engines provide "views" of the single schema and pretend > that these views comprise "multiple schemata", however, they are not. > They are simply a filtered view of the single schema for the database. > With these systems (such as Oracle, SQL Server, etc etc etc) the "schema" > you choose simply imposes a "filtered view" and "defaults" on certain > attributes of the real single schema. For example, there is an attribute > (column) in the schema called "schema name". > > > > When you choose a schema then all you get to see are the things where > the column [schema name] is equal to what you set, plus the dbo. When you > create a new object, the [schema name] is automagically set to be the name > of the schema you are choosing to use. The magic of the schema table > queries make it "appear" as if the items in your chosen [schema name] > obscure the remainder of the schema, but this is only an illusion created > by the WHERE clause. > > > >> But then I also want to be able to make queries across tables when I > >> do a query that says after time X, I want it to show me all rows -- > >> obviously different schema doesn't make this possible. > >> > >> So, In my application code I'm going to through loop through all the > >> tables. The table count is usually high hundreds (~600-800) > >> > >> I'm wondering if other people do this kind of stuff. Or another option > >> is to duplicate the data in some non schematized format (like a > >> summary table of sorts that essentially duplicates every row in every > >> table) > > > > I really don't understand what you are trying to accomplish. Are you > truing (for some unexplained reason) to "partition" data that otherwise > would belong in one table into a large number of tables for some reason -- > for example, instead of having a column [Colour] which describes the > colour of the thing the tuple is describing, instead have a bunch of > tables called Red, Yellow, Green, Blue (etc), so that you have to search > all of them instead of just one? > > > > > > > > > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
