flexible table in SQL

Question

I have a form php page that sends information about jobs. Each user can register unlimited jobs, so I need a flexible table in my database that when user insert new job, the table will get the values and save the information as job 1,job 2 ... job n.

Is there a way or maybe a simpler way?

Add more info, like tables used, sample data, expected result... — jarlh, Oct 28 '15 at 11:51
@jarlh: what info u need? i just want a flexible table in sql. is that unclear or hard? people just like to dislike a post it is when they do not know anything of coding. i just asked a simple question bah!! — sammy, Oct 28 '15 at 12:00
This sounds like you are heading towards some really bad database design. Constantly bumping out the number of columns to max(count(jobs)) is going to do nothing but cause you heartache down the line. Instead you should have a table that has `user | job` where a `user` has many records. — JNevill, Oct 28 '15 at 12:01
@sajad, to answer you question, Yes, "Flexible table" is unclear. That's not an actual term that I, personally, have ever heard. It could probably use some definition in your question like "A table whose columns grow dynamically to account for the maximum number of jobs submitted for any single user" or something along those lines. I might be wrong and maybe "Flexible Table" is a thing in some RDBMS, but my gut tells me it's probably not a thing. — JNevill, Oct 28 '15 at 12:18

score 1 · Accepted Answer · answered Oct 28 '15 at 12:12

1

Just to expand on my comment:

Your best bet here is a table for users:

user_id | name | other | attributes | for | a | user | like | birthdate

And a seperate table for the user's relationship to a job

user_id | job_id

If you need to store information about a job, then do it in yet another table

job_id | job_name | other | attributes | for | a | job

Now your database schema doesn't have to change dynamically as new data comes in. The only thing that grows is your record count. This is proper database design. You can query for all the job names associated to a user:

SELECT user.name, job.job_name
FROM user
    INNER JOIN user_job ON user.user_id = user_job.user_id
    INNER JOIN job ON user_job.job_id = job.job_id
WHERE user.user_id = 123

The direction you were heading where your schema changes as new data comes in will cause you nightmare scenarios down the line and should be avoided at all costs. For instance, imagine the same query where the number of job columns grows as new data comes in:

SELECT user.name, user.job1, user.job2, user.job3... how do I know where to stop for this user?
FROM user
WHERE user_id = 123

answered Oct 28 '15 at 12:12

JNevill

46,980
4
38
63

thanks friend, ur answer is simple and will help me too much, so i need three table, can u please tell me what is | for | a | user | like | ? thanks friend u r a great man... – sammy Oct 28 '15 at 12:28
ok i got , u mean was any attribute that i can add, correct? – sammy Oct 28 '15 at 12:29
1

Yep, just whatever attribute. A table's record should be a 1:1 type deal. So if your table is `user` it should only contain attributes of a `user`. Since user:job is 1:many, then `job` gets it's own table with it's own attributes. Glad it was helpful! – JNevill Oct 28 '15 at 12:32
which ones are primary keys? – sammy Oct 29 '15 at 07:11
1

I would probably use userid for the user table, both userid and jobid for the relationship table, and jobid for the job table. – JNevill Oct 29 '15 at 10:30
can you take a look here: http://stackoverflow.com/questions/33550554/insert-to-all-cells-when-records-are-more-than-one-php – sammy Nov 06 '15 at 07:50

flexible table in SQL

1 Answers1