Avoiding Multiple IDs

sfmnetsys

What is the best method for avoiding multiple ids for the same information.

For example: I have a table name company that has unique ids. The CompanyID are tied to a table name contacts which have unique ids (ContactID). The problem seems to be that if I add a contact with an existing CompanyName then I will get multiple ids for the same CompanyName.

I've considered using a drop-down menu for CompanyName when adding/modifying contacts, but it seems as if I might run into the same problem of multiple ids.

Anon

Multiple ID's where? If your contacts are linked to a company by a company id then your contacts table will have multiple company id's, it has to or you will only be able to have 1 contact for each company.

You should only have 1 company id for each company in the companies table, and 1 contact id for each contact in the contacts table, but you can have duplicate company id's in the contacts table so that you can have multiple contacts for each company.

kparker

If you have separate tables for 'company' and 'contact', you shouldn't have a problem, as long as it is clear (on the html forms the user is actually interacting with) whether the intent is to add a new company + contact, vs add a new contact with an existing company.

When you're adding another contact to an existing company, you simply don't INSERT anything into the company table; the contact table gets a new row added with the company ID being set to the ID of the company.

I'm assuming a table structure something like this:

create table company
(
id int auto_increment,
name text
...
);

create table contact
(
id int auto_increment,
company_id int, // references company.id
name text,
phone ...
);

This design assumes that a company can have any number of contacts, but each contact relates to exactly one company.

sfmnetsys

Very true.

What is the best method of adding, in this case, the CompanyID to the contacts table? I have created an array that prepopulates a drop-down menu with CompanyNames from another form that adds new CompanyName(s) to the company table. How do I get the CompanyID from the drop-down menu into the contacts table when I add the new row from the new contact? Also, will the same code work when the CompanyID associated with one contact is updated with another CompanyID?

kparker

How do I get the CompanyID from the drop-down menu

The displayable content and the value of the option don't have to be the same. I.e. you can do this:

  <select name="companies">
  <option value="1001">Company One
  ...
  </select>

That way, the user sees "Company One" but the value returned by the POST variable is 1001, which you can either use directly, or verify that it's at least a valid company ID before using (depending on your paranoia level.)

Also, will the same code work when the CompanyID associated
with one contact is updated with another CompanyID?

I'm not quite following what is changing here. If it's company ID's then the answer is that these should never change. If they can, then they're not suitable for linking between tables.