MySQL: Storing variable pairs of values in multiple tables

Original problem here.

I'm creating a form in CodeIgniter that has a paired value- Companies, and CEOs. The idea is, while there is only one Company field with one CEO field below initially on the form, there is a button for the 开发者_运维知识库user to create more pairs of Company-CEO fields. Also, there will be another page, where I'll take the input from the form and output it something like this:

CEO X is head of Company A

As such, this does mean I'm going to have to cause these pairs to associate with each other to some degree. Based on earlier advice, my Main MySQL table will look something like this:

id int
Attribute1  blob
Attribute2  blob

And then I will have a Company table:

id int
Company text
foo blob

Finally, I will have a CEO table:

id int
CEO text
foo blob

The idea is for simplicity's sake, for each record from Main, it should be simple enough to figure out which Company-CEO pairs belong to that record, since they all share the same id. That id is created every time the form is filled out and submitted. However, what method could I use to associate each Company with its corresponding CEO?

YOu're not storing anything in the main table that would identify the ceos/companies associated with the record, unless you're storing it in the blob fields. If you want to allow multiple ceos for a company, you'd need

company table (id, companyname)
ceo table (id, ceoname)


company_ceos (ceo_id, company_id)

and most likely would want some date information in there so you can say that "John Doe" was CEO from '99 to '08, and "Jane Smith" is ceo from '09 -> present

Put a foreign key column in the CEO and Company tables to link them to the Record id:

Company:

id int
Company text
record int
foo blob

CEO:

id int
CEO text
record int
foo blob