This is a hypothetical situation I have derived in order to get some assistance for a problem which I can then relate to the database I am creating.

I have some tables similar to these...

Books(ItemNo, Title, Author, Publisher, Year_Pulished)
DVD(ItemNo, Title, Released, Rating)
CD(ItemNo, Title, Artist, Year_Released)
Customer(CustNo, FName, LName)

And then this table which in the example i'm using on this forum stores orders...

Orders(CustomerNo, ItemNo, Type, OrderDate)

The CustNo relates to to the CustomerNo so in the SQL I would write....

CREATE TABLE Orders
(....
FOREIGN KEY (CustomerNo) REFERENCES Customer (CustNo)
....
);

But then the ItemNo must relate to all three tables. Writing the following does not work....

CREATE TABLE Orders
(....
FOREIGN KEY (ItemNo) REFERENCES Books,
FOREIGN KEY (ItemNo) REFERENCES DVD,
FOREIGN KEY (ItemNo) REFERENCES CD
....
);

Because this means that nothing can be entered as the value must be in all the tables and that defeats the object. So if you enter the ItemNo of a DVD and error says that this ItemNo does not appear in Books for example.

Any ideas how I can correct this?

Secondly (and less importantly), the 'type' field would store like 'DVD', 'Book', 'CD'...could this be done automatically? It's just to help with future queries.