Hello all -

I am writing a table where I am using a hierarchical structure to
create a series of nested questions. My structure is this:

id | parent_id | question
----------------------------------
1 | NULL | "Yes or no?"
2 | 1 | "Orange or Yellow?"
3 | 3 | "Sugar or cream?"

I'm just getting into foreign keys on InnoDB tables. I'm wondering if
I can create the parent_id column as a foreign key in the same table,
that would make it easier for me to delete an entire branch of
questions.

Can I create a foreign key in the same table? I need root questions to
have a NULL parent_id. Does a foreign key constraint prevent me from
having a NULL parent_id?

If I can't create a foreign key in a hierarchical structure, what's
the best way to delete an entire branch of questions? Currently I am
using a looping code structure where I descend and find the leafs,
delete them, and then work my way back up. This would be a lot easier
if I could use a foreign key and just delete the root question.

On Nov 7, 3:26 pm, lawp...@gmail.com wrote:
>
> I'm just getting into foreign keys on InnoDB tables. I'm wondering if
> I can create the parent_id column as a foreign key in the same table,
> that would make it easier for me to delete an entire branch of
> questions.

Did you try doing it? If you had you'd find that you can.

>
> Can I create a foreign key in the same table? I need root questions to
> have a NULL parent_id. Does a foreign key constraint prevent me from
> having a NULL parent_id?

Did you try that, too? Again, if you had, you'd find that it also
works.

On Nov 8, 8:38 am, ZeldorBlat <zeldorb...@gmail.com> wrote:
> On Nov 7, 3:26 pm, lawp...@gmail.com wrote:
>
> Did you try doing it? If you had you'd find that you can.
>
> Did you try that, too? Again, if you had, you'd find that it also
> works.

Okay, but why does it work? Isn't the idea of the foreign key column
to guarantee that the value in the foriegn key column exists in the
referenced column?

Does NULL mean that there is no value, therefore there isn't a value
in the column that does not exist in the referenced column? I.e. for a
foriegn key column value, the value must either already exist in the
reference column, or else be NULL, even if a NULL value does exist in
the reference column?

lawpoop@gmail.com wrote:
> On Nov 8, 8:38 am, ZeldorBlat <zeldorb...@gmail.com> wrote:
>> On Nov 7, 3:26 pm, lawp...@gmail.com wrote:
>>
>> Did you try doing it? If you had you'd find that you can.
>>
>> Did you try that, too? Again, if you had, you'd find that it also
>> works.
>
> Okay, but why does it work? Isn't the idea of the foreign key column
> to guarantee that the value in the foriegn key column exists in the
> referenced column?
>
> Does NULL mean that there is no value, therefore there isn't a value
> in the column that does not exist in the referenced column? I.e. for a
> foriegn key column value, the value must either already exist in the
> reference column, or else be NULL, even if a NULL value does exist in
> the reference column?

Might I respectfully refer you to:

http://dev.mysql.com/doc/refman/5.0/...eign-keys.html
http://dev.mysql.com/doc/refman/5.0/...eign-keys.html
http://dev.mysql.com/doc/refman/5.0/...nstraints.html

On Wed, 07 Nov 2007 20:26:15 -0000, lawpoop@gmail.com
wrote:

>Hello all -
>
>I am writing a table where I am using a hierarchical structure to
>create a series of nested questions. My structure is this:
>
>id | parent_id | question
>----------------------------------
>1 | NULL | "Yes or no?"
>2 | 1 | "Orange or Yellow?"
>3 | 3 | "Sugar or cream?"
>
>I'm just getting into foreign keys on InnoDB tables. I'm wondering if
>I can create the parent_id column as a foreign key in the same table,
>that would make it easier for me to delete an entire branch of
>questions.
>
>Can I create a foreign key in the same table? I need root questions to
>have a NULL parent_id. Does a foreign key constraint prevent me from
>having a NULL parent_id?
>
>If I can't create a foreign key in a hierarchical structure, what's
>the best way to delete an entire branch of questions? Currently I am
>using a looping code structure where I descend and find the leafs,
>delete them, and then work my way back up. This would be a lot easier
>if I could use a foreign key and just delete the root question.

Theoretically, for purists, this schema is not normalized.

Think of it as this:
A table is a common name for a relation.
The table you have here represents two different and
incompatible relations:
- the relation between an id and a question,
- the relation between an id and its parent.

Correcting this would give:
t1
id | question
-------------------
1 | "Yes or no?"
2 | "Orange or Yellow?"
3 | "Sugar or cream?"

t2
id | parent_id
--------------
2 | 1
3 | 3

Quote:
SQL’s “Nulls” Are A Disaster

http://www.dcs.warwick.ac.uk/~hugh/T...hout-nulls.pdf
Yeah, I know, in real life it can be done.
--
( Kees
)
c[_] If I'm supposed to live every day like it's my
last, I want a gallon of morphine. (Russ Wallace) (#393)

On Nov 8, 5:49 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Wed, 07 Nov 2007 20:26:15 -0000, lawp...@gmail.com
> wrote:
>
>
> Theoretically, for purists, this schema is not normalized.
>
> Think of it as this:
> A table is a common name for a relation.
> The table you have here represents two different and
> incompatible relations:
> - the relation between an id and a question,
> - the relation between an id and its parent.
>
> Correcting this would give:
> t1
> id | question
> -------------------
> 1 | "Yes or no?"
> 2 | "Orange or Yellow?"
> 3 | "Sugar or cream?"
>
> t2
> id | parent_id
> --------------
> 2 | 1
> 3 | 3
>
> Quote:
> SQL's "Nulls" Are A Disaster

Thanks, Kees, this is exactly what I was looking for. Is there a name
for this type of relationship, or a common naming convention for these
two tables? E.g. "many-to-many" is a common name for a intermediary
table that joins two tables. Is there also a common name for table 2
is your example?

On Nov 8, 5:49 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
>
> Theoretically, for purists, this schema is not normalized.
>
> Think of it as this:
> A table is a common name for a relation.
> The table you have here represents two different and
> incompatible relations:
> - the relation between an id and a question,
> - the relation between an id and its parent.
>
> Correcting this would give:
> t1
> id | question
> -------------------
> 1 | "Yes or no?"
> 2 | "Orange or Yellow?"
> 3 | "Sugar or cream?"
>
> t2
> id | parent_id
> --------------
> 2 | 1
> 3 | 3
>

Another question:

With the table structure described above, how would you delete an
entire tree of questions?

If you delete a root question in the questions table, a delete can
cascade to the relationships table, deleting one row. But then, the
delete from the relationships table cannot cascade back to the
question table -- leaving the first node with no record of a parent
question, and it thereby becomes a root question. Am I right?

With this structure, it seems that I have to write code to loop
through and delete all of these questions with individual statements.
With the non-normalized structure shown earlier, I can delete the
entire tree with a single delete statement.

On Fri, 09 Nov 2007 15:55:29 -0000, lawpoop@gmail.com
wrote:

>On Nov 8, 5:49 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
>> On Wed, 07 Nov 2007 20:26:15 -0000, lawp...@gmail.com
>> wrote:
>>
>>
>> Theoretically, for purists, this schema is not normalized.
>>
>> Think of it as this:
>> A table is a common name for a relation.
>> The table you have here represents two different and
>> incompatible relations:
>> - the relation between an id and a question,
>> - the relation between an id and its parent.
>>
>> Correcting this would give:
>> t1
>> id | question
>> -------------------
>> 1 | "Yes or no?"
>> 2 | "Orange or Yellow?"
>> 3 | "Sugar or cream?"
>>
>> t2
>> id | parent_id
>> --------------
>> 2 | 1
>> 3 | 3
>>
>> Quote:
>> SQL's "Nulls" Are A Disaster
>
>Thanks, Kees, this is exactly what I was looking for. Is there a name
>for this type of relationship, or a common naming convention for these
>two tables? E.g. "many-to-many" is a common name for a intermediary
>table that joins two tables. Is there also a common name for table 2
>is your example?

Hm, I'm not aware of common naming conventions for these
tables.
You could call t1 Questions, and t2 Questions_Adjacency,
or Questions_Hierarchy.
--
( Kees
)
c[_] A closed mouth gathers no feet. (#153)

On Fri, 09 Nov 2007 18:05:27 -0000, lawpoop@gmail.com
wrote:

>On Nov 8, 5:49 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
>>
>> Theoretically, for purists, this schema is not normalized.
>>
>> Think of it as this:
>> A table is a common name for a relation.
>> The table you have here represents two different and
>> incompatible relations:
>> - the relation between an id and a question,
>> - the relation between an id and its parent.
>>
>> Correcting this would give:
>> t1
>> id | question
>> -------------------
>> 1 | "Yes or no?"
>> 2 | "Orange or Yellow?"
>> 3 | "Sugar or cream?"
>>
>> t2
>> id | parent_id
>> --------------
>> 2 | 1
>> 3 | 3
>>
>
>Another question:
>
>With the table structure described above, how would you delete an
>entire tree of questions?
>
>If you delete a root question in the questions table, a delete can
>cascade to the relationships table, deleting one row. But then, the
>delete from the relationships table cannot cascade back to the
>question table -- leaving the first node with no record of a parent
>question, and it thereby becomes a root question. Am I right?

I would have to try to confirm that, but I'm lazy, so I
guess you are right. Perhaps a trigger is useful?

>With this structure, it seems that I have to write code to loop
>through and delete all of these questions with individual statements.

That is more or less a disadvantage of the adjacency model
for hierarchies. The nested set model is simpler
sometimes.

http://www.developersdex.com/gurus/articles/112.asp
http://dev.mysql.com/tech-resources/...ical-data.html

>With the non-normalized structure shown earlier, I can delete the
>entire tree with a single delete statement.

That's nice. I would say: don't look any further
By the way, with my original remark I didn't mean to say
"completely normalized is always better".
It was meant to show that mixing two different relations
in one table can have its disadvantages.
--
( Kees
)
c[_] A closed mouth gathers no feet. (#153)