[thelist] yet another database/table relationship design question

[Chris Blessing]

Paul-

Many thanks for the reply.  I suspect you are correct, as I may have
actually made the relationships "flow" the incorrect direction!  I will play
around with it tomorrow @ work and let you know if that doesn't fix it.
What I probably did was draw the diagram improperly.  EVEN with all my tools
and what-not I still didn't put that down 100% the first time around I
guess. :)

Also thanks for the trigger trick, I will definitely be employing that.
You're right, I can't combine the tables --- too many differences between
the two product types.  In fact, Journals are their own type completely
while ancillaries consist of several sub-types.  Ah the minds in marketing.
=/

Rudy, thanks also for the clarifications.  I figured out what Paul meant but
it's always good to get a 2nd backing.

Thanks again to you both!

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

> Ummm.... no. If, in the quoted database design, you're doing
> something like
>     INSERT INTO Products(Product_Code) VALUES ('PRODUCT0001')
> then that should work.
>
> It sounds like (it would be helpful to see the exact error message) that
> you've got your foreign key the wrong way around. Product is the primary
> key table; Ancillaries should be the foreign key table. I suspect that you
> have this the other way around, or that you have created the FK between
> Product.Product_ID and Ancillaries.Ancillary_ID by mistake.
>
>
> > 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!
>
> Short answer? No. Not with foreign keys anyway. You need an FK from
> Product.Product_ID (primary) to Journals.Product_ID (foreign), and one
> from Product.Product_ID (primary) to Ancillaries.Product_ID (foreign),
> but you know this.
>
> Ideally you would then do an "reverse foreign key" between
> Journals.Product_ID and Ancillaries.Product_ID, saying "allow no
> duplicate values", but since I just made up "reverse foreign keys",
> and they do not in fact exist in SQL Server, you might have a bit of a
> problem there.
>
> If you need to enforce, DB-side, that no duplicate product_IDs exist
> in the Journals and Ancillaries table, then your best bet would be
> to create a trigger on each table, which checks the values, like so
> (my trigger syntax is a bit dodgy, check the manuals; I haven't done
> a trigger in a few months)
>
>
> CREATE TRIGGER TheMagicFarawayTrigger ON Journals FOR UPDATE, INSERT AS
>     IF EXISTS(
>         SELECT *
>         FROM Ancillaries
>         WHERE Ancillaries.Product_ID IN (SELECT Product_ID FROM Inserted)
>     )
>     BEGIN
>         -- couple of things you could do here.
>         -- rollback, maybe?
>         -- or RAISERROR?
>         -- or something else, depending on what you want to do.
>     END
>
>
> and, correspondingly, the opposite trigger on the Ancillaries table.
>
> Of course, there is an even simpler solution, but I assume you've
> discounted it already. If, say, the Ancillaries and Journals tables held
> fundamentally the same data, and the difference was purely
> conceptual, then
> you could merge them into one table, the AncillariesAndJournals table,
> which has either a "TypeID" or a bit flag or something which indicates
> if it's an ancillary or a journal (though if you actually CALLED
> the table "AncillariesAndJournals", and I ever had to work on that table
> after you, I would hunt you down like a dog because that's an AWFUL name,
> but you get the idea...).
>
> I'm guessing, though, that if you've made each one its own table, there's
> a good reason for that. It's probably an awful idea, I just thought I'd
> suggest it anyway!
>
> Hope this helps,
>
> Paul