Please kickstart me - MySQL How to have a variable set of input fields

[kajje]

Using PHP/MYSQL...

I'm working on an application for managing our huge database of contact companies and people.

For the first stage I was developing a simplistic Company+1-staff record, it helped us to combine all address books, spreadsheets, etc into one big database.

For the 2nd stage I would like to split up the Companies and Staff, having a Company record with Company details, and with several Staff records attached to it.

So far so good...

Now the Staff record has some fixed fields, but also 1-2-many other fields.

MR
John Doe
General Manager
[+] add

when pressed 'add' there is an extra line created 
+---------------+ +-----------------+
| input/text | |dropdown V Type |
+---------------+ +-----------------+

Type can be 'Fax','Tel','Skype','Mobile','Email', etc



I can manage all this except the way how I should handle the unknown amount of fields in the table.

Eventhough my time is limited (isn't that for us all...) I don't like turnkey solutions, I want to find things out most by myself. I'm completely blocked with this one though... Can somebody drive me in the right direction?

Thanks
Kajje

[rocknbil]

I wouldn't look to a variable number of fields in the main table. What you probably want here is a relational table, joined on some unique id. Using your example:

Companies and Staff, having a Company record with Company details, and with several Staff records attached to it.

So

table staff
id|company_id|user_id|first|last|position......

Type can be 'Fax','Tel','Skype','Mobile','Email', etc

<select name="type" id="type">
<option value="">Select</option>
<option value="1">Tel</option>
<option value="2">Fax</option>
<option value="3">Skype</option>
<option value="4">Mobile</option>
<option value="5">Email</option>
</select>

Store the types in their own table, editable by admin, so you can use an integer value for the type - this becomes very important in terms of performance when you go to do joins and searches. Alternatively, this could be a simple associative array somewhere, but that's not very user-manageable.

table contact_types
id|contact_id|user_id|type_id|contact_value

Example:
13265|452353|1234|5|[e-mail]

So the staff member can have unlimited contact types, and are joined on user ID. This can make for more complex joins, but it's not too difficult.

Note that you'll never have to physically store the textual value of "type_id" anywhere but in the types table/array. It's just referenced by an ID. So any time that type changes, your database requires no updating, the displays change with it.

[kajje]

Thanks Rocknbill, this approach sounds indeed like the way to go.

As a matter of fact, this is also the way to go for my other databases. I will need some serious redesigning, but it for sure will pay off in the future!

Thanks
Kajje