Hi there

> > could you please expand on this ?
> >
> > I am afraid I didn't understand.
> >
> > We start with this database:
> >
> > create table Stars (
> > Star_ID integer not null,
> > System_ID integer,
> > primary key (Star_ID)
> > )
> >
> > create table Systems (
> > System_ID integer not null,
> > Planet_ID integer not null,
> > primary key (System_ID,Planet_ID)
> > )

> If your system is identified by SYSTEM_ID alone, why isn't it the Primary
> Key then?

> From your metadata, rows in table SYSTEMS are identified by SystemID and
> PlanetID.

I'm going to take a bit of a guess here, but a planetary system could
surround a binary star, which means there would be the following set of
relationships:

star to system is n to 1
planet to system is n to 1

If there is no need to store information besides an ID about each system
(perhaps the name is generated from the stars involved by some algorithm)
there is no reason to have the Systems table - the Stars and Planets tables
will have all you need. If you want additional information about each
system (say a number) then you need the following schema - otherwise just
leave out the Systems table.

CREATE TABLE Stars (
Star_ID INTEGER NOT NULL,
Name VARCHAR(40),
System_ID INTEGER
PRIMARY KEY (Star_ID)
);

CREATE TABLE Planets (
Planet_ID INTEGER NOT NULL,
Name VARCHAR(40),
System_ID INTEGER
PRIMARY KEY (Planet_ID)
);

CREATE TABLE Systems (
System_ID INTEGER,
NAME VARCHAR(40),
PRIMARY KEY (System_ID)
);

You should be able to set up whatever foreign keys you want using this
schema.

Gary