(Python, sqlite3) Building relational database?

Question

I am trying to create a relational database in python with sqlite3. I am a little fussy on how to connect the tables in the database so that one entity connects to another via the second table. I want to be able to make a search on a persons name via a webpage and then find the parents related to that person. Im not sure if I need two tables or three.

This is how my code looks like right now:

class Database:
 '''Initiates the database.'''
 def __init__(self):
 self.db = sqlite3.connect('family2.db')
 def createTable(self):
 r = self.db.execute('''
 CREATE TABLE IF NOT EXISTS family2 (
 id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
 fname TEXT,
 sname TEXT,
 birthdate TEXT,
 deathdate TEXT,
 mother TEXT,
 father TEXT
 )''')
 self.db.commit()
 g = self.db.execute('''CREATE TABLE IF NOT EXISTS parents(
 id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
 mother TEXT,
 father TEXT)''') 
 self.db.commit()
 b = self.db.execute('''CREATE TABLE IF NOT EXISTS relations(
 id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
 family2, 
 parents TEXT
 )''')
 self.db.commit() 

Thanks in advance!

Answer 1

You don't need multiple tables; you can store the IDs of the parents in the table itself:

CREATE TABLE persons(
 id INTEGER PRIMARY KEY,
 name TEXT,
 mother_id INT,
 father_id INT
);

You can then find the mother of a person that is identified by its name with a query like this:

SELECT *
FROM persons
WHERE id = (SELECT mother_id
 FROM persons
 WHERE name = '...')

• user3018178: I appreciate the prompt response, but this solution is what I have come up with already. I want to learn about relational databases and thus want two tables in my database.
• CL.: Two tables do not make sense when you have only one object type (person).