On Fri, Apr 11, 2008 at 01:54:43PM -0700, Richard Klein scratched on the wall:
> Jay A. Kreibich wrote:
> >On Thu, Apr 10, 2008 at 05:58:59PM -0700, Richard Klein scratched on the
> >wall:
> >
> >>My advice would be to try it and see. If table creation takes too long,
> >>you can always remove th
Thanks for all the suggestions. My schema is now a lot cleaner, and my
application runs 30% faster!
Jeff
Richard Klein wrote:
>> Jeff Gibson wrote:
>>
>>> One thing your earlier suggestion brought up. The way I was hooking up
>>> tables before was something along the lines of:
>>>
>>>
Jay A. Kreibich wrote:
> On Thu, Apr 10, 2008 at 05:58:59PM -0700, Richard Klein scratched on the wall:
>
>> My advice would be to try it and see. If table creation takes too long,
>> you can always remove the UNIQUE constraint, and then write a routine to
>> check the table for uniqueness after
> Jeff Gibson wrote:
>> One thing your earlier suggestion brought up. The way I was hooking up
>> tables before was something along the lines of:
>>
>> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, );
>> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
>> CREATE TABLE link(id1 INTEGER, id2 INTE
Jeff Gibson wrote:
>
> One thing your earlier suggestion brought up. The way I was hooking up
> tables before was something along the lines of:
>
> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, );
> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, );
> CREATE TABLE link(id1 INTEGER, id2 INTEGER)
On Thu, Apr 10, 2008 at 05:58:59PM -0700, Richard Klein scratched on the wall:
> My advice would be to try it and see. If table creation takes too long,
> you can always remove the UNIQUE constraint, and then write a routine to
> check the table for uniqueness after it's created.
That "routine
Jeff -
I'm pretty new to databases myself, but I have seen examples of
schemas that resemble your first alternative, i.e. that involve
the creation of a third table containing just the linkages
between the primary and secondary tables.
And in fact, I think that is the right solution for your appl
Right. Hence my hesitation. :-) I suppose it's possible to check
uniqueness once at the end in O(N), but it would also take O(N) extra
storage, and I doubt sqlite is doing that...
One thing your earlier suggestion brought up. The way I was hooking up
tables before was something along the li
On second thought, checking the entire table for uniqueness would seem
to require O(N log N), regardless of whether it is done one INSERT at
a time, or all at once after the table is created!
- Richard
Richard Klein wrote:
> Quoting from the description of CREATE TABLE in the SQL Syntax section o
Quoting from the description of CREATE TABLE in the SQL Syntax section of
the SQLite documentation:
"The UNIQUE constraint causes an index to be created on the specified columns.
This index must contain unique keys."
The creation of an index would seem to imply an O(log N) search on each
insertio
I see. Fortunately my application simplifies this since the database is
created once and read many times, but is never modified after creation
time. Regarding constraints, I was thinking it might be helpful to add
a few where applicable (whether foreign key constraints or even simple
uniquene
Jeff,
I think that's the right way to go for your application. There are a few
things you should be aware of regarding this approach.
A column in one table that references a column in another table is called
a "foreign key" in database lingo.
An issue with foreign keys is that it is important t
Thanks! I'll give that a try.
Jeff
Richard Klein wrote:
>> Whether or not the the secondary columns are needed is a function of one
>> of the primary columns. That function involves values from another
>> table, though, so the general case would require a join. That other
>> table is smal
> Whether or not the the secondary columns are needed is a function of one
> of the primary columns. That function involves values from another
> table, though, so the general case would require a join. That other
> table is small, however, so I generally cache it outside the database.
> Som
Whether or not the the secondary columns are needed is a function of one
of the primary columns. That function involves values from another
table, though, so the general case would require a join. That other
table is small, however, so I generally cache it outside the database.
Some pseudoco
> I'm pretty new to databases, and I have a schema design question. I
> don't know enough about the guts of how sqlite works to know how to make
> some tradeoffs. I have a large (potentially millions of entries) table
> and it has 4 columns which are needed for every entry, and 4 more that
I'm pretty new to databases, and I have a schema design question. I
don't know enough about the guts of how sqlite works to know how to make
some tradeoffs. I have a large (potentially millions of entries) table
and it has 4 columns which are needed for every entry, and 4 more that
are need
17 matches
Mail list logo
