Hi,
I'am new to this forum(ofcourse new to DB Design too).
But, i've taken up to complete a DB design for storing related information.
I've almost completed - but got stuck up at a critical point.
If someone can help me cross this issue - it wud be great.

Let me explain the design situation i have :
3 entities are involved :
1. PrimaryContainer
{
id_PK : int (Unique integer PK for identifying each 'PrimaryContainer' object)
[Some attributes for primary container]
}

2. SecondaryContainer
{
id_PK : int (Unique integer PK for identifying each 'SecondaryContainer' object)
[Some attributes for secondary container]

primaryContainerFK : int (refers to id_PK of 'PrimaryContainer' object)
}
[SecondaryContainer has a many to one relatioship with the 'SecondaryContainer' - i.e., many 'SecondaryContainer' objects can be hierarchially placed under a 'PrimaryContainer' object]

3. TransferObject
{
id_PK : int (Unique integer PK for identifying each 'TransferObject' object)
[Some attributes for transfer object]
primaryContainerFK/secondaryContainerFK : int ???????????????<-PROBLEM
}

4. CommonAttributes
{
id_PK int [PK : Unique identifier for each common resource]
name : varchar
owner : varchar
}

There are certain entities 'MainContainer'(not given), 'PrimaryContainer','SecondaryContainer', 'TransferObject'.., : which all have
certain information (name, owner..,) in common(but unique) - hence each of them share a one-one relationship with the 'CommonAttributes' table.

Now, the problem I have is :

I have to relate each 'TransferObject' instance with either a 'PrimaryContainer' instance / 'SecondaryContainer' instance(to be placed under either of them and not both) - This can be done through the id_PK field for each container(primary/secondary). Now i'am not sure of how to relate the 'TransferObject' instance with either a 'PrimaryContainer' / 'SecondaryContainer'.

Please suggest some way out..

Thanks in advance,
Baskar

Could you please state the problem you are trying to solve, in English, using non-technical language?

What you have presented is a solution of your problem, not the problem itself. As such, it is practically impossible to figure out what you are trying to solve. Therefore, I suspect, little help will be forthcoming.

Ravi

well, ravi, it's not that hard

he's got a table which he wants to relate with a foreign key to one or the other of two tables

baskar, use two foreign keys, one to the PrimaryContainer table, the other to theSecondaryContainer table

allow them both to be null, because on any TransferObject row, one of them will be

Well, I realized ages ago the "technical" issue. But that still does not answer my question of the problem that is being presented here.

What exactly is the problem that the user has presented to him? Why does he have these four tables? Meaningful solutions to the problem can only be suggested when the problem is known.

To suggest that a foreign key be created (or two, or three) is merely mechanical implementation. That is not "design". To design something one needs to understand the problem.

I am not brave enough to venture trite solutions when I do not even understand the problem. Looking at the four tables he has shown does not really tell me anything. Is an alternative design better? I have nothing to go on.

Ravi

you're calling my solution trite?

okay, that's it, swords or pistols, your choice, dawn tomorrow, yonge and bloor, bring someone to carry your sorry corpse home afterwards

Agreed!

But, after I die, I don't have to worry about my body.

Please do not disturb me for the remainder of the day (= remainder of my life!) since I'll be busy writing my last will and testament.

Wow, very authentic! So what's the weapon? How about yellow pads and pencils?

Hi all,
First of all - thanks for a preliminary analysis of my problem.
Well, it may be a fairly simple question - for DB design experts in the forum.
Unfortunately, I'am not an expert - but trying to become (as I said - I've just started on DB design..)
And let me not be reason for fights in the forum. I'll explain my problem with more clarity.(Hi Ravi !! I hope you are a very good problem analyzer ???)

And the SQL consultant - just replied what ever I had in mind - but felt a bit
childish to propose in the forum.

Let me just explain it all over again :
There are certain entities(say A,B,C,D) - with common attributes. Hence - I formed an entity(COMMON) for common attributes and moved all the common attributes to that entity. Now - all other entities share a 1-1 relation with the common entity.(This is over- & cannot be changed now

Apart from that, the entities A,B,C,D - have an hierarchial relation among each other. It is :
'A' is the root. 'A' can contain a number of instances of entity 'B'.
And 'B' can contain a number of instances of 'C'.

Now : the problem is placing the entity 'D' s intances :
Each instance of 'D' can be hierarchially placed under - either 'B' or 'C'.

So I have to set the foreign key relation from 'D' with the tables 'B' or 'C'- without adding any new table or modifying any of the existing ones other than 'D'.

I just had an idea like the 'SQL Consultant' to have 2 foreign keys in 'D' - one
for 'B' and another for 'C'. So, whichever is not applicable can be 'null'.

For this - I had 2 doubts in mind :
(I'am using entity EJBs for each of these - entities)

1. Whether the design of having 2 FKs allowing nulls a standard approach ?
2. While programming - I may have to check for the availability of FK in entity 'D' to either entity 'B' or entity 'C'. The doubt in my mind is : There is a free chance for both the FK to be null - which creates a situation - that remains unhandled in my program .

I hope - now I have stated my problem clearly - for you all to advice.

Hoping to get some advice out of your experience !!!

Thanks all!!
Baskar

please, call me rudy


1. Whether the design of having 2 FKs allowing nulls a standard approach ?

yes, it is


2. While programming - I may have to check for the availability of FK in entity 'D' to either entity 'B' or entity 'C'. The doubt in my mind is : There is a free chance for both the FK to be null - which creates a situation - that remains unhandled in my program .

the way to handle all three conditions is to use a query which joins table D to both C and B using LEFT OUTER joins

that way, each D will be returned to your program with the C or B that it belongs to (or with neither, if it has neither)

I'll have to implement it using EJB CMP beans... (I don't directly interact with the tables using SQL)
Thanks Rudy - for giving the suggestion about 2 FKs.

you have my sincere condolences

LoL!

Hey Rudy, where were you this morning? Couldn't see you at Yonge and Bloor.

Anyway, the FKs issue is fine. One thing, you may want to ensure is that exactly one of the foreign keys is filled in. This can be done by adding a check constraint to the table that does this.

Baskar, now that we have more information, it looks like you have designed your Java clasess first and then decided to do a one-to-one mapping to database tables. The concept of aggregation, or entities containing other entities, is very unusual in database design, and suggests that alternative approaches must be tried.

Relationships between entities are enforced through foreign keys.

Looks like you are trying to enforce a type/sub-type (mutually exclusive) relationship between entities D (as parent) and B and C as children. That does not have a clean solution. You are forced to use database code to enforce it.

Ravi

Seems like if he'd used GUIDs as IDs, he could have a single key refer to multiple tables...

blindman, have you actually tried your proposed solution?

here, allow me to give you a head start:okay, these are the two tables, and now we set up a third, which will have, as you suggested, "a single key refer to multiple tables" --and now, i invite you to try to enter a row into table C

i'll give you a hint: if it'll work for your fabulous GUIDs, it should work for a simple VARCHAR key, right?

It seems that blindman was just making fun.

But then, if you presented this solution to OO fanatics, their eyes would light up. "Wow!," they'd say, "what a brilliant idea! Why didn't I think of it? This way, I never have to deal with stupid FKs. Relational database theory is so outdated. Those poor losers!" (Actually, Scott Ambler, another Toronto native and XP ultra-fanatic and self-proclaimed former data modeller, really calls database people "losers". He is a columnist and author, some of his writings can be found at http://www.sdmagazine.com/thinking/)

What an OO fanatic would say is that you do not need FKs at all. Since all PKs are unique, given a key used as a foreign key, we can find what table it is related to, simply by querying all the tables in the database and finding out if any of them has this particular key as its PK.

Voila! Foreign keys are redundant. Not only that, we now have the great flexibility of relating any table to any other table! Isn't that great?

That this is a stupid idea in many respects would never strike them.

Obviously, I strongly disagree. OO has its uses, but is vastly over-hyped. In the next 5-10 years you will see it lose its lustre. A few chinks in the armour are already appearing through the use of Aspect Oriented Programming, the redesign of EJB 3.0 specs, the dissatisfaction with Java 1.5 generics as providing little benefit for a lot of additional syntax, the increasing popularity of multi-paradigm languages such as Python and Ruby, the increasing interest in Smalltalk and possibly Lisp, etc. I wouldn't be surprised if today's OO experts start claiming, a few years from now, that they weren't really that enamoured of OO after all.

Anyway, I thought this would be a good forum and topic to vent my feelings about OO fanaticism.

No offence intended for anyone.

At work, I use databases (Oracle mainly), Java (JSP and Java Beans, not EJBs), XML and XSLT. I choose the tool depending on the requirements, and do not force the problem to fit my preferred tool. For personal work, I prefer Python. Still a novice at Python, though.

Ravi