CODEWITHSUNDEEP
mysqlarraysdatabase
nubicurio
nubicurio

Reputation: 165

MySQL (involving arrays) Table Suggestion Needed

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.

Upvotes: 0

Views: 102

Answers (4)

Gajendra Bang
Gajendra Bang

Reputation: 3583

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

Upvotes: 1

Janaki
Janaki

Reputation: 195

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

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

Upvotes: 1

Sevag Akelian
Sevag Akelian

Reputation: 231

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.

Upvotes: 1

Pedro Ferreira
Pedro Ferreira

Reputation: 649

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

Table 1 | UserId | Name | Email | Address |

Table 2 | UserId | Time | Employer | Position |

Upvotes: 1

Related Questions