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
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
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
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
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
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
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
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
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
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
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
> 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.
>
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
> 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
17 matches
Mail list logo
