SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Foreign key - multiple parents

    I have used MySQL in my developments for years. But I've never really tried to use the SQL properly to define the relationships between tables. I just set up the tables and defined primary and secondary indexes as necessary and let the code accomodate the dependencies. I have started a new development where it would be extremely advantageous to define the tables properly.

    My first challenge is the following. I have two tables, a company table and a user table. Both companies and users have addresses. I want to set up a single address table to hold addresses for both companies and users. Simple thing to do until I tried to define my first foreign key. I am at a loss.

    My understanding is that the child table identifies the parent key. Simple enough for a relationship between two tables. In my case in the address field I have a field which identifies whether the address comes from the company table or the user table. Then a second field contains the key of the parent.

    I suppose one answer is to have a company address table and a second table for user address. But one function I need to have is to select all users and companies within x miles of an address. If I had all the addresses in one table it would be easier.

    In consideration I suppose it may be more practical to put the street address and zip in the user and company tables and then have a zip code table with the city and state. But it seems I would still have the same problem of two tables pointing to the zip code table.

    I have a similar situation with phone numbers. Except in this case I will have a variable number of phone numbers. Instead of having 5 possible phone fields on both the company and user I would rather have a single phone number table with however many phone numbers existing for each company or user.

    What is the best/most practical way of setting up this relationship in the SQL? Am I just trying to be too complex with the SQL?

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,194
    Mentioned
    17 Post(s)
    Tagged
    4 Thread(s)
    What you trying to do is called a polymorphic association and in *most* cases it is best avoided. The reason it is best avoided is for the reason you have ran into – not possible to define a true foreign key.

    One approach to the problem is use subtypes making the address the parent table and each address type a child table.

    address
    - address_id (pk)
    - city
    - state
    - ...

    company_address
    - company_address_id (pk)
    - address_id (address foreign key)

    user_address
    - user_address_id (pk)
    - address_id (address foreign key)

    The other option is to reverse the relationship. Instead of placing the user/company foreign key within the address table place it inside the user and company table.

    address
    - id
    - city
    - state
    - ...

    company
    - name
    - address_id (address foreign key)

    user
    - name
    - address_id (address foreign key)

    That is assuming each user and company has one address. Otherwise you could use a look-up table to associate each to their given addresses.

    * The book: SQL Antipatterns goes over these alternatives to polymorphic association in more detail if interested.
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Member
    Join Date
    Feb 2011
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your input. I thought I was pretty comfortable with normalization until I started getting into Spring Scaffold and realized I needed to get more precise with my DDL. I did come up with an alternative to this. For the most part the street address is unique to each user or company. Of course company employees may have the same address as the company, but then maybe not. So I decided to make street address on the company and user. Then I built a zip table with a forein key on each table pointing to the zip table. This was my best solution anyway as I need to have zip codes with lat/lon so I can calculate the distance between zips. Sometimes you have to ask a question about a problem before you can see better solutions yourself.

    I do have another issue that looks like I'll just have to code through lookups. I prefer to build table driven applications. No hard coded descriptions. If a field can have 10 values I want those values in a code table where I can modify the description or add or delete codes without program changes. I usually develop one codes lookup table containing all the possible codes and their descriptions. To enable foreign keys I would have to have a code table for each code field. There could be 30 or 40 of them. I'd rather have them in one field and handle it through application control structure.

    This is a fun development. I have developed application in Struts 1 and EJB 2. I am trying to move up to EJB 3, Struts 2, JPA and Spring 2. Getting my database orgainized has taken far too long just trying to get the relationships defined through the DDL.

    Again thanks for the advice.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by JavaGuyKy View Post
    To enable foreign keys I would have to have a code table for each code field. There could be 30 or 40 of them. I'd rather have them in one field and handle it through application control structure.
    this is the OTLT ("one true lookup table") anitpattern, also to be avoided
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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