Two one-to-many relations with three tables

Question

import sqlite3
# get connection and cursor objects
conn = sqlite3.connect('iodatabase.sdb')
c = conn.cursor()
# create tables
c.execute('''create table grand_parent (
 id integer primary key autoincrement,
 name text
)''')
c.execute('''create table parent (
 id integer primary key autoincrement,
 name text
 grand_parent_id text,
 FOREIGN KEY(grand_parent_id) REFERENCES grand_parent(name)
)''')
c.execute('''create table child (
 id integer primary key autoincrement,
 name text,
 module text,
 type text,
 desc text,
 parent_id text,
 FOREIGN KEY(parent_id) REFERENCES parent(name)
)''')
c.execute("INSERT INTO grand_parent VALUES(null, 'AS1')")
c.execute("INSERT INTO parent VALUES(null, 'Parent1', 'AS1')")
c.execute("INSERT INTO child VALUES(null, 'Child1', 'AO', 'CVXY', '1', 'Parent1', 'AS1')")
c.execute("INSERT INTO child VALUES(null, 'Child2', 'AO', 'CVXY', '1', 'Parent1', 'AS1')"
c.execute("INSERT INTO child VALUES(null, 'Child3', 'AI', 'FTRE', '1', 'Parent1', 'AS1')"
c.execute("INSERT INTO child VALUES(null, 'Child4', 'AI', 'FTRE', '1', 'Parent1', 'AS1')")
c.execute("INSERT INTO parent VALUES(null, 'Parent2', 'AS1')")
c.execute("INSERT INTO child VALUES(null, 'Child1', 'AO', 'CVXY', '1', 'Parent2', 'AS1')")
c.execute("INSERT INTO child VALUES(null, 'Child6', 'AI', 'FTRE', '1', 'Parent2', 'AS1')")
c.execute("INSERT INTO child VALUES(null, 'Child4', 'BO', 'MESR', '1', 'Parent2', 'AS1')")

Hi All,

I have three tables. One will be grand parent, one will be parent and the last one will be child table. I mean, I want my child data to know which parent and grand parent it belongs to. Also, I want my parent data to know which grand parent it belongs to. I tried to do it by myself. But I couldnt. How should I set up the relations between the tables? How should be the table structures?

Thanks in advance.

EDIT

Ignore the code. Just set up three tables in raw sql so that the required relation is met. This is the first time I do such a thing and I need guidance.

• gurney alex: please rename your tables to grandparent, parent and child in your example. Please tell us with code what you would like to get and how you fail.
• Shansal: I think I will go for tree structure in database by using triggers
• Eric Fortin: Using foreign key on text field is asking for trouble I think. Normally you link on id.

Answer 1

If I understanded:

the problem is here: FOREIGN KEY(parent_id) REFERENCES parent(name) and would be FOREIGN KEY(parent_id) REFERENCES parent(id). Reference ID instead NAME.

Referencing the parent table, through a query you is able to return the grandparent record from the child table.

Please comment if it is not what you are looking for.