[thelist] yet another database/table relationship design question

[Chris Blessing]

I certainly owe a tip at this point don't I? :)  BTW this is a SQL Server
issue, but relates to general db design.

So I've got this table, Products.  It's the heart of the database I'm
designing.  Off this table are several child tables, and off those are even
more child tables.  But we're only concerned with the 1st level of child
tables in this instance.

Here's the basic layout:

Products
- Product_ID PK identity
- Product_Code

	Journals
	- Journal_ID PK identity
	- Product_ID FK (to Products)

	Ancillaries
	- Ancillary_ID PK identity
	- Product_ID FK (to Products)

Now the problem here is that I've imposed a UNIQUE constraint on both
Product_ID FK's in Journals and Ancillaries.  This means the current
relationship between both those tables and their parent table, Products, is
1-1.  This is how I want it to be, no dupes!

However I'm running into an issue when trying to insert data into the
Products table.  If I try to insert a Product_Code into Products, I get a
foreign key constraint conflict in the Ancillaries table.  I assume SQL
Server would throw the same error on the Journals table if it was the only
child table of Products.  Should I be getting this error?

My general assumption is that SQL Server is expecting the Product_ID
(auto-generated as an identity in Products) to appear in the Ancillaries
table when I do the insert into the Products table.  This isn't going to
always be the case, since a "Product" (a Product_ID) can appear in EITHER
the Journals or Ancillaries table, but the Journals.Product_ID and
Ancillaries.Product_ID fields are mutually exclusive.  In other words, the
same Product_ID from Products won't appear in both Journals and Ancillaries,
it'll be in one or the other.

So to ask my real question, how can I create such a mutually exclusive
relationship?  Or do I need to do something completely different?  Any
insights are greatfully welcomed!

Chris Blessing
webguy at mail.rit.edu
http://www.330i.net

<tip type="Product recommendation: a tool to help you design a database"
author="Chris Blessing">
There are certainly some great tools out there for designing database
diagrams.  The best one I've found thus far, with the most usable
functionality and smallest learning curve, is SmartDraw
(http://www.smartdraw.com/downloads/download.asp).  It includes all the
standard ERD diagram symbols for entities, relationships, labels, etc.  A
very handy piece of software, and there's a free trial download!  Check it
out the next time you need to get a really detailed visual representation of
the database you're about to create.
</tip>