Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Design Issue

  1. #1
    Join Date
    Dec 2004
    Posts
    6

    Design Issue

    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

  2. #2
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Wow, very authentic! So what's the weapon? How about yellow pads and pencils?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Dec 2004
    Posts
    6
    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
    Last edited by baskar007; 12-05-04 at 05:12.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by baskar007
    And the SQL consultant
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Dec 2004
    Posts
    6
    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.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by baskar007
    I'll have to implement it using EJB CMP beans... (I don't directly interact with the tables using SQL)
    you have my sincere condolences
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249
    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

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Seems like if he'd used GUIDs as IDs, he could have a single key refer to multiple tables...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    blindman, have you actually tried your proposed solution?

    here, allow me to give you a head start:
    Code:
    create table A
    ( foo varchar(9) not null primary key
    )
    insert into A(foo) values ('curly')
    insert into A(foo) values ('larry')
    insert into A(foo) values ('moe')
    
    create table B
    ( bar varchar(9) not null primary key 
    )
    insert into B(bar) values ('tom')
    insert into B(bar) values ('dick')
    insert into B(bar) values ('harry')
    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" --
    Code:
    create table C
    ( fubar varchar(9) not null primary key
    , foreign key (fubar) references A(foo)
    , foreign key (fubar) references B(bar)
    )
    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?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Seems like if he'd used GUIDs as IDs, he could have a single key refer to multiple tables...
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •