On Wed, Sep 16, 2009 at 8:57 AM, Kavita Raghunathan
<kavita.raghunat...@skyfiber.com> wrote:
>
> ----- Original Message -----
> From: "Pavel Ivanov" <paiva...@gmail.com>
> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
> Sent: Wednesday, September 16, 2009 6:40:18 AM GMT -06:00 US/Canada Central
> Subject: Re: [sqlite] Schema and database layout
>
>> KR: The 10 tables contain different data, but instead of doing this
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the
>> entity-id.
>
> You better stop telling puzzles and explain everything in thorough
> details so that we could understand it.
> "entity-id" - what is it? There's no such column in the table, you've
> never spoken about any entity, so we can just guess... "this table
> "type" - what table type? Give the definition please what you're
> calling "table type". "I want to just pass this table "type" - pass
> what, where and doing what? What are you doing that is hard and with
> "passing table type" will be easier?
> And continuing to look into my crystal ball I still don't understand
> why my suggestion doesn't suit you. You have 10 tables with exactly
> the same structure, exactly the same set of columns but with different
> types of data. But type of data doesn't matter when it comes to
> storage of this data in the database. The only thing that matters here
> is the set of columns. So you can create table like this:
>
>>KR:
> I apologise, I did not intend to be vague; you made me realize how
> vague I was. Also, I'm new to database design. I surely
> appreciate your help, Pavel.
>
> Here it is:
> I have 10 tables with the exact same schema:
> int, int, varchar(10), varchar(20). All the 10 tables have this same schema.
> Each of the 10 tables belongs to 1 entity, uniquely identified by an
> entity-id.
> I was planning on having 10 tables named like so: typenumber_<entity_id>.
> and not include entity_id into the schema.
> But from reading your email, it sounds like i could just have 1 table, and
> add entity id to it. So, add a 5th column like this:
> int, int, varchar(10), varchar(20), int(this last one is the entity id and
> it would be identical every 10 entries or so)
> Would this be a better design ?
Yes, this would be a better design.
> Can you point me to some database design docs that dictates that tables of
> the same type should be 1 single table ?
>
>
Google for help with designing relational databases. You will get 36
million hits.
>
> CREATE TABLE TypeNumbers (
> ID INTEGER PRIMARY KEY AUTOINCREMENT,
> table_type INTEGER,
> MainID INTEGER,
> Type INTEGER,
> CurrentNumber INTEGER);
>
> And here is you "table_type" that will have numbers 1 to 10 and you
> will be able "to pass it to SQLite" (whatever that means).
>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>
> It doesn't.
>
>> KR: Different threads are going to access different tables of the same table
>> type.
>
> Even more puzzles here. I thought your table type is just one table,
> it seems that you have several tables of the same type but why? And
> what's the difference between tables of different types? Does this
> mean that you have more than 10 tables? Do you have only 10 "table
> types"?
>
>> Maybe I need to think this through, even as I write this:-)
>
> That definitely will help. :-)
>
>
> Pavel
>
> On Tue, Sep 15, 2009 at 6:01 PM, Kavita Raghunathan
> <kavita.raghunat...@skyfiber.com> wrote:
>> Please see clarifications below:
>> ----- Original Message -----
>> From: "Pavel Ivanov" <paiva...@gmail.com>
>> To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
>> Sent: Tuesday, September 15, 2009 4:36:19 PM GMT -06:00 US/Canada Central
>> Subject: Re: [sqlite] Schema and database layout
>>
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables
>>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>>
>> Create 1 table with 1 additional column "schema" which will contain
>> numbers from 1 to 10.
>>
>> KR: The 10 tables contain different data, but instead of doing this
>> statement (example below) over
>> and over, I want to just pass this table "type" because all my tables look
>> exactly
>> like table "TypeNumbers", but they have different data, depending on the
>> entity-id.
>>
>> strcpy(sqlStr,"CREATE TABLE TypeNumbers (");
>> strcat(sqlStr,"ID INTEGER PRIMARY KEY AUTOINCREMENT,");
>> strcat(sqlStr,"MainID INTEGER,");
>> strcat(sqlStr,"Type INTEGER,");
>> strcat(sqlStr,"CurrentNumber INTEGER);");
>>
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>
>> Elaborate please what do you want to do, where do you want to pass
>> "schema" (do you mean table name here?) and data?
>>
>> KR: The above should answer this question.
>>
>>> 2)Also, do you reccomend a different .db file for each schema so that
>>> multiple threads can easily operate at the same time ?
>>
>> It depends on how your threads will operate, how often they will issue
>> sql statements, what type of sql statements and all other details
>> about your application functionality.
>>
>> KR: Different threads are going to access different tables of the same table
>> type.
>> Maybe I need to think this through, even as I write this:-)
>>
>> Thanks, Pavel!
>>
>>
>> On Tue, Sep 15, 2009 at 5:27 PM, Kavita Raghunathan
>> <kavita.raghunat...@skyfiber.com> wrote:
>>> All,
>>> I have a couple of questions:
>>>
>>> I've been studying the C/C++ interface, and
>>> 1) dont see a way to reuse schemas. In other words, I have say 10 tables
>>> with the same schema. How would I prevent doing 10 CREATE table commands ?
>>> Would there be a way I can pass the schema as well as data into SQLite ?
>>>
>>> 2)Also, do you reccomend a different .db file for each schema so that
>>> multiple threads can easily operate at the same time ?
>>>
>>> I apologise in advance if I missed it in the documentation.
>>>
>>> Thanks again to this group for your awesome support. I've got a lot of
>>> ideas from you all.
>>>
>>> Regards,
>>> Kavita
>>> _______________________________________________
>>> 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
>> _______________________________________________
>> 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
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
-----------------------------------------------------------------------
Assertions are politics; backing up assertions with evidence is science
=======================================================================
Sent from Madison, WI, United States
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
