[SQLite 3] Relational Databases??

[Lionofgod]

http://sql.learncodethehardway.org/book/learn-sql-the-hard-waych3.html
This is the exercise that I am doing, I've been learning SQl since I am looking into using databases for Python.

The problem is with the extra credit
"In these tables I made a 3rd relation table to link them. How would you get rid of this relation table person_pet and put that information right into person? What's the implication of this change?"

I tried doing it with just two tables as it asks, here is the schema

Code: [Select]

CREATE TABLE person(
id INTEGER PRIMARY KEY,
first_name TEXT,
last_name TEXT,
age INTEGER,
person_id INTEGER,
pet_id INTEGER
);
CREATE TABLE pet (
id INTEGER PRIMARY KEY,
name TEXT,
breed TEXT,
age INTEGER,
dead INTEGER
);
I created the database and everything, I inserted data into person and pet, here are my statements

Code: [Select]

INSERT INTO person(id, first_name, last_name, age) VALUES(0, "Zed", "Shaw", 37);
INSERT INTO pet (id, name, breed, age, dead) VALUES (0, "Fluffy", "Unicorn", 1000, 0);
INSERT INTO person (person_id, pet_id) VALUES (0,0);

The output that I get when I select * from person is very weird...

Code: [Select]

[/size][/font]

0|Zed|Shaw|37||
1||||0|0

Why are their bars where their is no data?
Is the way I tried making the relation wrong?
And can someone give me a hint as to "What the implications of this are"
EDIT: Code Highlighting for sql is weird so I didt use it

[dataspy]

I don't know python but try using one insert instead of two for person.  Also why do you have have person_id in the person table when you can use the id of that table person.id?

Insert pet info first, then get the pet id of that insert then insert person info with one insert, you don't need two person id fields.

Code: [Select]

INSERT INTO pet (id, name, breed, age, dead) VALUES (0, "Fluffy", "Unicorn", 1000, 0);
get id of pet insert and assign var
$pet_id = id
INSERT INTO person(id, first_name, last_name, age) VALUES(0, "Zed", "Shaw", 37, '$pet_id');


[xzid]

the output you're getting makes sense, you insert 2 records into "person":

Code: (sql) [Select]

INSERT INTO person(id, first_name, last_name, age) VALUES(0, "Zed", "Shaw", 37);
INSERT INTO person (person_id, pet_id) VALUES (0,0);
the | are just seperators for your values, you can change that output to make it more readable:

Code: [Select]

sqlite> .headers on
sqlite> .mode column
sqlite> select * from person;
id          first_name  last_name   age         person_id   pet_id
----------  ----------  ----------  ----------  ----------  ----------
0           Zed         Shaw        37                               
1                                               0           0         

to drop the person_pet table(assuming a person only has 1 pet), add a pet_id column to your person table.

I couldn't tell you what the implications are, I am not no such question ask me.

= nice for the syntax highlight but that light blue/gray is unreadable.