Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    2

    Relational data design problem...

    I am having an issue with determining how I should create table relationships. Here are the four tables I have (shown with abbreviations and only the relevant fields)...

    Company
    --------------------
    - CompId

    User
    --------------------
    - UserId
    - CompId

    Address
    --------------------
    - AddId

    CompAddLink
    --------------------
    - CompAddLinkID
    - CompID
    - AddID

    Now my issue is that a user can only have a relationship with an address if the company has a relationship with the address. Here are things to consider:
    1. If a CompAddLink is deleted, it should cascade

    2. A CompAddLink can not be updated if a user relationship exists

    So here are the options I am pondering:
    1. Create a link table between User and Address. This would require:
    > Update trigger on CompAddLink to prevent updates if a user is using this relationship
    > Insert/Update trigger on UserAddLink to validate that a user has rights to this address

    2. Create a link table between User and CompAddLink. Table would look like this:
    UserCompAddLink
    --------------------
    - UserCompAddLinkId
    - UserId
    - CompId
    - AddId
    > Create the following relationships:
    a. UserCompAddLink.CompId to CompAddLink.CompId
    b. UserCompAddLink.AddId to CompAddLink.AddId
    (meets needs but now I am duplicating data in my database and I try to avoid this)

    3. Create a link table between User and CompAddLink. Table looks like this:
    UserCompAddLink
    --------------------
    - UserCompAddLinkId
    - UserId
    - CompAddLinkId
    > Update trigger on CompAddLink to prevent updates if a user is using this relationship

    Now one common item in all three scenarios above is that I believe I will need is the use of a Delete trigger on CompAddLink to remove user relationships when a company looses rights to an address. To me scenario 1 seems the cleanest but utilizes 2 triggers when only 1 may be needed (using scenario 3). But I have never used a linking table to create a relationship to another linking table. It just seems wrong.

    Any help on this would be appreciated. Thanks in advance.

  2. #2
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18

    Address DB Design

    How about this:

    You have an AddressID field for both Company and User. You also have a flag field on the User to indicate that their address is linked to the Company address. Then, your business logic can take care of the rest. If the flag is set, anytime the company address changes, the user address will change with it. If it is not set, then a duplicate address should be stored to keep any changes in the company addresss independent of changes in the User address.

    This would also allow your User to add supplemental information (department, cubicle/office #) to the Company address when they are being created to make things more relevant from the beginning.

  3. #3
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18
    Let me add on to what I just said... So your design would really look like this:

    User
    -------
    UserID
    AddressID
    CompanyID
    CompAddrLinkFlg

    Company
    --------
    CompanyID
    AddressID

    Address
    --------
    AddressID


    You wouldn't need any other tables for this.

  4. #4
    Join Date
    May 2003
    Posts
    13

    Talking

    Use this approach and it will solve all your problems. Use Party table for anything, User, Company... I would use Related Party if any company is related to other company e.g. subsidiary...

    Enjoy
    Attached Thumbnails Attached Thumbnails Party.jpg  

  5. #5
    Join Date
    Jun 2004
    Posts
    2
    Thanks Ciperlad

    But each Company can "own" more than one address and each User can relate to one or more of them. So the AddressId in the Company and User tables does not solve that. And I do like the idea of allowing the User to add more information (I will have to remember that for my next project like this) but the actual purpose of this is assigning shipping addresses to a company. Then the sales manager has the ability to assign shipping locations to the sales users. Each salesperson is only allowed to ship from certain addresses.

    So I need to relate multiple Addresses to a Company and then allow a User to be related to any of the addresses that the Company relates to. My big question is how to keep the relationships valid from a DB level. I know I can enforce this using code, but that demoralizes part of my DB.

    PazPower,
    I appreciate your response as well, but again it only solves part of the problem. I need to make sure the tables can not have data that is not valid. And using your diagram I do not see how applying that to my problem would prevent the link tables from storing "bad" data.

  6. #6
    Join Date
    Jun 2004
    Location
    Kuala Lumpur
    Posts
    4
    I think you can solve this problem using a compound key - it's not as neat as it could be, but it's simpler than most of the other suggestions.

    Company
    ---------
    *CompanyId

    CompanyAddress
    ----------------
    *CompanyId
    *AddressId
    AddressDetails

    User
    -------
    *UserId
    CompanyId
    AddressId

    (* denotes Primary Key fields)

    So the User table uses both CompanyId and AddressId as its foreign key to the CompanyAddress table, and you can create a constraint on that. This prevents a User from having an address that isn't valid for their company.

    Is that the problem you wanted to solve, or have I missed an important requirement?

Posting Permissions

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