> 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
