Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2007
    Posts
    4

    Entity relationships - what's correct

    Let's say I have two entities, A and B that look like:

    Entity A
    --------
    ID - int primary key
    B_ID int foreign key references Entity B (ID)

    Entity B
    --------
    ID - int primary key
    A_ID int foreign key references Entity A (ID)

    The relationship can be established either when you create or update entity A or create or update entity B.

    My problem is that with this design, I have to know that when I update entity A to relate to entity B, I also have to update entity B to reflect this relationship. Same thing when I update entity B first.

    Is there a better design strategy for this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lfoulkrod
    Is there a better design strategy for this?
    can't answer that question without knowing what you're trying to do

    having A relate to B and also B relate to A is very, very unusual

    perhaps you could either (1) tell us your real tables and columns, so that we could understand the relationships better, or (2) give examples of several rows of actual data so that we can understand the relationships better
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Posts
    4

    re: Entity relationships - what's correct

    The real tables are types of assets, a computer for example. Table A represents a legacy asset, one that will be replaced by table B, the new asset. So they would look like:

    Legacy Asset
    -------------
    LAID int primary key
    Manufactuer
    Model
    etc...

    New Asset
    -----------
    NAID int primary key
    Manufacturer
    Model
    etc....

    Now when you enter a new asset into the system you can associate it with a legacy asset, but the same is true for when you enter a legacy asset in the system, you can associate it to an existing new asset. The relationship between assets is one to one.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by lfoulkrod
    The relationship between assets is one to one.
    ah, well, that's okay then

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

  5. #5
    Join Date
    Oct 2007
    Posts
    4
    It still seems kind of funky to me to have to update both tables when I create an association between the two entities. It seems more natural and less error prone to have the relationship defined in a single place. Right now it is:

    LegacyAsset
    ------------
    LAID int primary key
    NAID int foreign key (NewAsset.NAID)

    NewAsset
    ---------
    NAID int primary key
    LAID int foreign key (LegacyAsset.LAID)

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by r937
    ah, well, that's okay then
    It doesn't get my stamp of approval. How do you ensure that the correct records are pointing at eachother? Can an asset be replaced by more than one asset? Can an asset replace more than one asset? For instance, a fax and a printer could be replaced by a single faxprinter. How would that be represented.
    I do not think this is a good database design.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Oct 2007
    Posts
    4
    The current requirements is that one asset is replaced by another single asset. A fax by a fax, a printer by a printer. I didn't think it was good database design, that's why I created this thread and asked for suggestions on the correct design, do you have any suggestions?

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    My syggestion would be:

    LegacyAsset
    ------------
    LAID int primary key

    NewAsset
    ---------
    NAID int primary key
    LAID int foreign key (LegacyAsset.LAID)

    i.e. there is only one relationship, not two.

    To query which new asset replaces old asset 123

    Code:
    select naid from newasset where laid=123;
    To query which old asset was replaced by new asset 456

    Code:
    select laid from newasset where naid=456;
    What more do you need?

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by lfoulkrod
    The current requirements is that one asset is replaced by another single asset. A fax by a fax, a printer by a printer. I didn't think it was good database design, that's why I created this thread and asked for suggestions on the correct design, do you have any suggestions?
    I suggest you reevaluate your requirements, because that seems pretty ignorant of real-life business rules to me.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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