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. 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. 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
