> Would this be a better design ?
That would be definitely a better design at least because you will be
able to keep only one prepared statement where one of bindings will be
your entity id. Also you will be able to add more types of entities
without changing database schema. Just don't forget to add proper
index where first field will be entity id.
But each design has its caveats of course. And if your application
works in 10 threads with shared cache mode turned on and each thread
updates or adds only one type of entities then with 10 different
tables you'll have better concurrent updates than with 1 table. It
doesn't matter though if your application works in 10 different
processes.
> Can you point me to some database design docs that dictates that tables of
> the same type should be 1 single table ?
Sorry, can't help with this.
Pavel
On Wed, Sep 16, 2009 at 9: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 ?
> Can you point me to some database design docs that dictates that tables of
> the same type should be 1 single table ?
>
>
>
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
