Modelling question with identifying relationship

[Joseph Hesse]

I want to create tables Parents and Children. I started with what I
have below and then used MySQL Workbench.

CREATE TABLE Parents
(
ParentID INT NOT NULL,
Name VARCHAR(45) NOT NULL,
PRIMARY KEY (ParentID)
);

CREATE TABLE Children
(
ParentID INT NOT NULL,
Name VARCHAR(45) NULL,
INDEX (ParentID)
);

Workbench allowed me to make Children.ParentID a foreign key but it was
a non-identifying relationship.

When I tried, in Workbench, to make the relationship identifying,
Workbench forced Children.ParentID to be a primary key. This is a
problem because if Children.ParentID is a primary key then there can't
be duplicate Children.ParentID in the Children table.

I suppose I could fix things by adding an additional primary key to
Children but it doesn't seem necessary.

Why can't I do what I originally intended?

Thank you,
Joe

[Erick T. Barkhuis]

Joseph Hesse:

>CREATE TABLE Parents
>(
> ParentID INT NOT NULL,
> Name VARCHAR(45) NOT NULL,
> PRIMARY KEY (ParentID)
>);
>
>CREATE TABLE Children
>(
> ParentID INT NOT NULL,
> Name VARCHAR(45) NULL,
> INDEX (ParentID)
>);

>When I tried, in Workbench, to make the relationship identifying,
>Workbench forced Children.ParentID to be a primary key.

What is, in your opinion, an "identifying relationship"? Or differently
phrased: would these tuples be "identifying" enough?

Parents.ParentID Children.ParentID Children.Name
====================================================
5 5 Peter
5 5 John
5 5 James
5 5 Peter
5 5 Peter



--
Erick

"We're going to turn this team around 360 degrees." - Jason Kidd

[Joseph Hesse]

The above tuples look OK. MySQL allows one to set the following on an
identifying relationship. A row from the Parents table can't be removed
without first removing the corresponding children from the Children
table. This seems like a good safety measure.

[The Natural Philosopher]

It is necessary, to uniquely identify a given child.

For example Fred (parent) may have two children called 'Mark'.

The are bnoth called mark, they both have the same parent id.

Use an arbiratrary primary key - I suggest 'id' in the child.

Its a lot easy to say 'where child.id='3076' than
'where child.name=mark and parent.name=fred and child.parentid=parent.id
and mark.haircolor='red'

> Why can't I do what I originally intended?
>
> Thank you,
> Joe
>

--
To people who know nothing, anything is possible.
To people who know too much, it is a sad fact
that they know how little is really possible -
and how hard it is to achieve it.

[Jerry Stuckle]

You need to uniquely identify rows in the children table. The ParendID
won't work - parents can have multiple children.

Name won't work because you could have two different sets of parents,
each with a child named "John". And if you have the whole name (which
you need - what if the child gets married and changes his/her name, for
instance?), you could still have two "Jane Smith"s (from different
parents). So Name won't work.

You *could* have a combination key of Parent Id/Name? It's a
possibility. At first you might think it's unlikely for parents to have
two children with the same name. But what about the case where a guy
has a son named "John Smith" from a first marriage - then marries "Jane
Doe". Jane also has a son named "John Doe"; the guy adopts the son
officially and changes his name to "John Smith". Now he has two sons
with the same name. Unlikely, I admit - but these are the things you
need to think about when designing tables.

All in all, I think the best would be to create an autoincrement column
to give the child a unique id. It's really not that much more overhead
and solves a lot of potential problems.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

[Erick T. Barkhuis]

Joseph Hesse:

>... MySQL allows one to set the following on

>an identifying relationship. A row from the Parents table can't be
>removed without first removing the corresponding children from the
>Children table. This seems like a good safety measure.

Well, if _that_ is what you want, then

- use InnoDB engines for both tables
- define FOREIGN KEY (ParentID)
REFERENCES Parents(ParentID)
ON DELETE RESTRICT
for the Children table.



--
Erick

[Thomas 'PointedEars' Lahn]

Joseph Hesse wrote:

> When I tried, in Workbench, to make the relationship identifying,
> Workbench forced Children.ParentID to be a primary key. This is a
> problem because if Children.ParentID is a primary key then there can't
> be duplicate Children.ParentID in the Children table.
>
> I suppose I could fix things by adding an additional primary key to
> Children but it doesn't seem necessary.
>
> Why can't I do what I originally intended?

An identifying relationship is not the kind of relationship that you have in
mind here. That is, in your relationship the child can exist without the
parent. You would have known that had you RTFM or STFW before.

Also, if your example was taken literally, you should only use one table,
because the children can have children themselves and thus can be parents.

Your question is not MySQL-specific and should have been posted to
comp.databases, if that.

--
PointedEars

Please do not Cc: me. / Bitte keine Kopien per E-Mail.