Given the object model, I want to design the database tables. The object
model is that school has a list of courses.

The following are my 2 approaches. My question is which one is better?
Should table COURSE has the foreign key of table SCHOOL? Or table SCHOOL has
the
foreign key of table COURSE?

Please advise the design principles here. Thanks!!

Approach #1: table COURSE has the foreign key of NAME in table SCHOOL
================================================== ==
CREATE TABLE SCHOOL
( NAME varchar(50) not null,
PRINCIPAL varchar(12),
PRIMARY KEY(NAME)
);

CREATE TABLE COURSE
( NAME varchar(50) not null,
SCHOOLNAME varchar(50),
FOREIGN KEY(SCHOOLNAME) REFERENCES SCHOOL(NAME),
PRIMARY KEY(NAME)
);

Approach #2: table SCHOOL has the foreign key of NAME in table COURSE
==================================================
CREATE TABLE COURSE
( NAME varchar(50) not null,
PRIMARY KEY(NAME)
);

CREATE TABLE SCHOOL
( NAME varchar(50) not null,
PRINCIPAL varchar(12),
PRIMARY KEY(NAME),
COURSENAME varchar(50),
FOREIGN KEY(COURSENAME) REFERENCES COURSE(NAME),
);

It depends what you mean by course, if you mean a specific course at a
specific school and a school has many courses i.e a specific course it
taught at one specific school, but a school may teach many courses
then #1 i.e. each entry in course table is the name of the course and
the link to the school that teaches it.

If you mean subject like maths or english and a schools teach many
subjects and a subject is taught by many schools then neither, you
need another table to hold this information.

see

http://www.brunel.ac.uk/~eestcjb/EE2002S/L07.ppt

I think this is pretty much lifted from Object Oriented Modelling and
Design - Rumbaugh.

Responding to Matt...

The short answer is: if you are designing a DB, then use Data Modeling. B-)

In this case the rules for placing the referential attribute happen to
be the same and they are dictated by the multiplicity and conditionality
of the relationship.

1 *
[School] --------------- [Course]

The referential attribute must go on the [Course] side. That's the only
place it is unambiguous.

* 1
[School] --------------- [Course]

The referential attribute must go on the [School] side. Ditto.

* *
[School] --------------- [Course]

One needs an association object (index) rather than referential
attributes in the DB. Embedded referential attributes would be
ambiguous on either side.

0..1 1
[School] --------------- [Course]

It should go on the [School] side. The issue here is not ambiguity; it
just makes processing navigation slightly less expensive because there
is no need to check if the relationship is "active" before navigating it.

[Caveat: if navigation is always from the [Course] side, then the
performance benefit for placing it there will usually outweigh the
conditional checks. That, however, becomes academic if one provides
things like auxiliary indices to support joins.]

1 0..1
[School] --------------- [Course]

It should go on the [Course] side. Ditto.

1 1
[School] --------------- [Course]
or
0..1 0..1
[School] --------------- [Course]

It can go on either side. Typically that choice is made based upon DB
performance by placing it on the side from which which navigation is
most common when forming joins. (As a practical matter it is likely
that auxiliary indices will be provided to address join performance.)


*************
There is nothing wrong with me that could
not be cured by a capful of Drano.

H. S. Lahman
hsl (AT) pathfindermda (DOT) com
Pathfinder Solutions -- Put MDA to Work
http://www.pathfindermda.com
(888)-OOA-PATH

Check out this excellent article by Mr. Scott Ambler
http://www.agiledata.org/essays/mappingObjects.html
It addresses this issue. Like the other poster said, it depends on
what you are trying to do with your data modeling. This paper suggests
that you would have the one side of the table have a foreign key
towards the many side of the table. I took a quick glance and did not
see any explanation for why. If you can find it, please share it.

Bala
69BP1N-NCRDU
http://uml.zepho.com