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
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
