0

I'm trying to create a PHP form on a website that collects users' name, email, address, and their work experience. Name, email, and address all pretty straightforward and have their own field, but the work experience table consists of three fields: "time period", "employer" and "position", and multiple rows will be dynamically added by the user as required (a JavaScript is used to accomplish this on the website).

My question is, what is the best way to model this in the MySQL database? Do I use just one table like this:

| UserId | Name | Email | Address | Time | Employer | Position |

Or should I use two tables like this:

| UserId | Name | Email | Address |

| UserId | Time | Employer | Position |

My guts are telling me that the second solution might be what I need, since I don't know how to add dynamic arrays into a database. But how do I associate the same UserIds of the second table to the first table? If I stick to the first solution using only one table, is it possible to save arrays of Time, Employer and Position under one UserId? Keep in mind that I have no way of knowing in advance how many rows of work experience users will be creating.

Improve this question

4 Answers 4

Reset to default
1

Option 2 is the correct since it would create much less redundancy.

Table 1 | UserId | Name | Email | Address |

Table 2 | UserId | Time | Employer | Position |

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

So if Bonnie submitted 4 employment records along with her name, email and addy using the website, how do I link those 4 rows in the second table to her UserId automatically generated by the first table (let's say her UserId is 10)?
actually they already are linked through the UserId, with sql JOIN you can efficently generate views like | UserId | Name | Email | Address | Time | Employer | Position |
as Lucy stated, a join will do the job. You can find a tutorial on Joins here
Edit: I will try to learn more about JOIN! Say this is what Bonnie have submitted, Name: Bonnie, Email: [email protected], Address: Sesame Street 101. Work experience: 1. 2000-2005, Disneyland, Micky Mouse; 2. 2005-2009, Nickelodeon, Spongebob; 3. 2009-2012, Starbucks, Clerk. Does that mean when the form is submitted, all her three employment records would automatically get a UserId 10?
I have a feeling that I might not have expressed myself clearly. I'm trying to figure out how to save the form submitted by different users to my database. How many rows each user will generate is unknown beforehand...
|
1 
Table 1     | UserId | Name | Email | Address |

Table 2  |Id| UserId | Time | Employer | Position |

Option 2 better , and if you don't want to get into the join things, just grab the $userId from the 1st table and do a single query on the 2nd table (WHERE UserId = '$userId'). This will return you an array of all the work experience of the user.

Improve this answer

5 Comments

I'm still trying to figure out how to save these records into the database; so there are 10 people that filled out the form on the website; the 1st person has 8 rows of work experience, the 2nd has 2, and the 3rd has 4...etc. How does second table know which rows belong to which user?
in the 2nd table there's the "UserId". so the 1st person will have 8 rows in the 2nd table with all the rows having the same UserId (the person's id). Before you insert the data in the 2nd table , insert the Name, Email, Address in the 1st table. Then do a query on the 1st table (WHERE Email = "his email address"). The returned array will have a UserId attribute, save this attribut in a variable, and do a 2nd query on the 2nd table (WHERE UserId = $userid). I can't explain more :P
This is what I'm trying to do using PHP to save the records: INSERT into Table1 (name, email, address) VALUES ('Bonnie', '[email protected]', 'Sesame Street 101'), the second query would be INSERT into Table2 (time, employer, position) Values('something', 'something', 'something'). Can you tell me how I can loop through all 8 rows and insert them into the second table, and all still have the same user ID as generated by the first table? I'm still not sure how (WHERE Email = "email") should work since I have no way of knowing what the email would be when user enters her email:(
before inserting anything in the second table you notice that the information is inserted in the 1st table, so you can query back the inserted record in the 1st table .you have the email address
I guess this is where I was not being clear about (my bad!) When the user submits the form from the website, all information should be automatically saved into the database, on the fly, as users enter and submit theirs data. That's kinda what my question is about... The website will be made available online, and I have no way of knowing when the form will be filled out...
1 
Table 2  |Id| UserId | Time | Employer | Position |

This would be the correct option I guess.. since it would create less redundancy.

Improve this answer

Comments

1

from Sevag Akelian's answer, you will need to use join to get data. it will be something like this

select u.userid, time, employer, position from user as u left join
user_experience as ue on u.userid = ue.userid

and if you want to get data for a particular user, just use condition

    select u.userid, time, employer, position from user as u left join
    user_experience as ue on u.userid = ue.userid
where userid = 1
Improve this answer

3 Comments

Could you help me with the "Insert" statement for PHP when the form is submitted? That's kinda where I'm having trouble with...
You will have to use two insert queries. first for user table and then you will need to get userid (max id or mysql_insert_id function). Then use this userid to insert records in user_experience table. If you can provide the exact table schema, I can help you
Sorry for my late response; been away for a few days. I'm going to open up a new thread as Pedro requested. I've figured out how to do what you suggested, but have encountered a new problem. Thanks again g.b.1981!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.