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?
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.
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.