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

Reply via email to