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?