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:
>>>
>>> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, <primary_fields>);
>>> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, <secondary_fields>);
>>> CREATE TABLE link(id1 INTEGER, id2 INTEGER);
>>>
>>> My understanding of your suggestion is:
>>>
>>> CREATE TABLE primary(id1 INTEGER PRIMARY KEY, id2 INTEGER,
>>> <primary_fields>);
>>> CREATE TABLE secondary(id2 INTEGER PRIMARY KEY, <secondary_fields>);
>>>
>>> with the understanding that id2 in primary will often be NULL. Are
>>> there any circumstances where the first alternative is more
>>> appropriate? I'm pretty new to databases, but I got my original idea
>>> from a few schemas that I've seen. I'm just trying to understand the
>>> trade-offs.
>>> Thanks a lot for your help,
>>> Jeff
>>>
>>>
>> These different forms of linking the records are used for different
>> types of relations. The two tables can have records that are related in
>> a various combinations of one or many to one or many.
>>
>> one to one
>> many to one
>> one to many
>> many to many
>>
>> Using a third table is required to implement a many to many relation.
>> Each record in the third table stores one item of the relation (i.e
>> which record in the first table is related to which record in the second
>> table).
>>
>> A one to many relation is created by assigning an id to the record in
>> the one side of the relation and referencing that id in a column on the
>> many side of the relation. A many to one relation is the same a one to
>> many relation, with the order of the tables reversed. This is what you
>> have shown as Richard's suggestion.
>>
>> A one to one relation can be created by assigning an id to one record
>> and using that same id as the primary key on the related record.
>>
>> For your case, you need a one to one relation between the primary and
>> secondary tables. This can be done by using the same id for the related
>> record in the secondary table as was assigned to the record in the
>> primary table.
>>
>> CREATE TABLE primary(id INTEGER PRIMARY KEY, <primary_fields>);
>> CREATE TABLE secondary(id INTEGER PRIMARY KEY, <secondary_fields>);
>>
>> insert into primary values(null, <primary fields>);
>> insert into secondary values(last_insert_rowid(), <secondary fields>);
>>
>> When you want to retrieve the records you can use a join
>>
>> select * from primary join secondary using(id);
>>
>> or you can use a second select to retrieve the secondary fields using
>> the id obtained from the primary field.
>>
>> select * from primary;
>> if (has_secondary())
>> select * from secondary where id = primary.id;
>>
>> This does not waste any space storing unnecessary null fields. You
>> should only resort the more complex foreign keys when you need to
>> represent a more complex relation.
>>
>> HTH
>> Dennis Cote
>>
>
> As Dennis points out, I had assumed that the relationship between the
> primary and secondary tables was many-to-one, i.e. that several entries
> in the primary table could refer to the same entry in the secondary
> table.
>
> If that is not the case -- if the relationship is in fact one-to-one --
> then Dennis's solution is the best one.
>
> I would use Dennis's two-SELECT approach rather than the join if speed
> is an issue.
>
> - Richard
> _______________________________________________
> 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
