On Sun, 10 Sep 2017 20:35:16 -0700, Vikas Aditya <vikas.adi...@quikfynd.com> wrote:
> Hi All, > > I need some help in figuring our right query syntax for querying > items from two tables. We have two tables. One of the table has > list of items. And Second table has additional attributes. Adding to the suggestions of Ryan Smith, I would suggest a few table definition optimizations: > CREATE TABLE ITEM ( > ID INTEGER, > FIELD0 TEXT > FIELD1 TEXT, > FIELD3 TEXT, > FIELD4 TEXT > ); By adding 'PRIMARY KEY to the ÍD column, it becomes an alias for the internal ROWID column, saving space. Also, to reference a parent table, the reference needs to point to a unique column. A primary key fulfills that requirement. CREATE TABLE ITEM ( ID INTEGER PRIMARY KEY, FIELD0 TEXT FIELD1 TEXT, FIELD3 TEXT, FIELD4 TEXT ); > CREATE TABLE ATTRIBUTES ( > ID INTEGER PRIMARY KEY, > ITEM_ID INTEGER, > KEY TEXT, > VALUE TEXT > ); Do the same for the primary key of the attributes table. Add a foreign key constraint to formalize the relation between the parent and child table. This provides "referential integrity". The index will often speed up JOIN operations. CREATE TABLE ATTRIBUTES ( ID INTEGER PRIMARY KEY, ITEM_ID INTEGER REFERENCES ATTRIBUTES ON DELETE CASCADE, KEY TEXT, VALUE TEXT ); CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID); Even better, the ID column can be left out, and a different primary key will enforce that the same KEY can only be used once for any ITEM_ID: CREATE TABLE ATTRIBUTES ( ITEM_ID INTEGER REFERENCES ATTRIBUTES(ID) ON DELETE CASCADE, KEY TEXT, VALUE TEXT, PRIMARY KEY (ITEM_ID,KEY) ) WITHOUT ROWID; CREATE INDEX attr_item_id ON ATTRIBUTES(ITEM_ID); Note: Foreign key constraints are only enforced when PRAGMA foreign_keys=on; is executed after opening the database, before any INSERT/UPDATE/DELETE statement is issued. HTH -- Regards, Kees Nuyt _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
