CODEWITHSUNDEEP
mysql
Frederik Witte
Frederik Witte

Reputation: 1345

Database referencing columns from tables?

I am sure what I am trying to do is basic practise, but I can't find the solution for this. I searched about foreign keys but I don't think this is what I need here.

My desired behaviour: I have an app which has a "locations" tab and a "contact" tab. This data will be pulled from a database. The information will be filled in in a cms. There are two things a user can choose for the contact: Choose existing Contact from location(which pulls the name + mail) or create a new one. In my file, I am then pulling the data from my contact table and just display the rows in select elements.(see below for a short example).

So in my contact table I need to have two things: Either a reference to the name + mail from my location table or a new record(custom mail + name)

I have two tables:

+-------------------+
| LOCATION          |
+-------------------+
| primary           |
| name              |
| zip               |
| mail              |
| ...               |
+-------------------+

+-------------------+
| CONTACT           |
+-------------------+
| primary           |
| (lid)             |
| name              |
| mail              |
+-------------------+

The relationship between location and contact is a one to one/none relationship. So there can be locations without a contact.

The contact can be two things: a reference to a location(which then pulls name + mail from there), so that when a user updates a location, the contact will automatically update, or create a "custom" contact.

Ex:

User creates location with: name "Test" mail "[email protected]"

User selects "Test" for the creation of a new contact which: pulls "Test" and "[email protected]" as name and mail from location table(also updates it when the location is updated)

User creates contact with: name "Custom" mail "[email protected]"

I then have a contact form, which pulls in the data from the contact table, so that it then displays:

<select>
    <option>Test</option>
    <option>Custom</option>
</select>

How can I achieve this? I know the solution, if I would just need one of the examples. Creating a table where a user can insert custom data, for example, is easy practise. But how can I combine a "custom" and a "relational" functionality?

Thanks in advance!

Upvotes: 0

Views: 35

Answers (1)

Ian Kenney
Ian Kenney

Reputation: 6426

Did you consider normalising the database so that contacts are stored in the contacts table and a reference to the contact record in held in the location table. 

+-------------------+
| LOCATION          |
+-------------------+
| id                |
| contact_id        |
| zip               |
| ...               |
+-------------------+

+-------------------+
| CONTACT           |
+-------------------+
| id                |
| name              |
| mail              |
+-------------------+

When amending a location, If the user selects an existing contact, you can store the id in contact_id. If they are adding a new one, then have the application first save the new contact and then use this newly created contact's id

Upvotes: 1

Related Questions