Question relations tables

[Remco Swoany]

hi,


I have a user-table described as below. That is filled in trough the
sign-in form and available through the edit-method.

Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | | |
| email | varchar(128) | NO | | | |
| hashed_password | varchar(64) | YES | | NULL | |
| enabled | tinyint(1) | NO | | 1 | |
| profile | text | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| last_login_at | datetime | YES | | NULL | |
| |
+-----------------+--------------+------+-----+---------+----------------+

Now i want also that the user can fill in the following things:

a)work experience
b)studies
ect.

Must i add a new column in the user-table or make a new table and join
it into the user table?

If you recommended a second table, what is the process to do that?



Grtz..remco

 

[Ron Fox]

Since a user can have several work expeiences and several studies, I'd
be going for a pair of tables:

work experience table with the fields:

id primary key auto-increment
userid foreign key that links back to the id in the user table.
work-experience information colums that depend on what information
you want stored about the work experience item e.g. start date, end
date, employer, salary, reason for leaving, description etc. etc.

Similar structure for the studies table, where the education item
fields are also probably different e.g.
Institution, degree, date obtained, field of study etc. etc.


You don't give enough context for me to know how to you want these
tables created/defined? mysql? oracle? sqlite? Rails?

 

[Remco Swoany]

Since a user can have several work expeiences and several studies, I'd
be going for a pair of tables:

work experience table with the fields:

id primary key auto-increment
userid foreign key that links back to the id in the user table.
work-experience information colums that depend on what information
you want stored about the work experience item e.g. start date, end
date, employer, salary, reason for leaving, description etc. etc.

Similar structure for the studies table, where the education item
fields are also probably different e.g.
Institution, degree, date obtained, field of study etc. etc.


You don't give enough context for me to know how to you want these
tables created/defined? mysql? oracle? sqlite? Rails?

Hi Ron and Felix...thanks for the reponse

Ron...i am using mysql as my database. Yesterday i created a new column
"experience" in the "user" table and attached to the new table
"experience" and defined the model in rails. This works fine...but based
on you're post..with in the context "userid foreign key that links back
to the id in the user table"...i realize that what i created is not
correct way.

Questions..

Why use "foreign key that links back to the id" ?

Dou you have a example of a rails-migration-schema with the create table
definitions (incl. foreign key that links back to the id in the user
table)?

The data capturing of the user...must being just by the consumer..so
they can make selections. examples > i want a person with business
degree and 2 years work experience. Must you think about this process
before you create the database-schema?

Thanks advance!!!!

Grtz..remco

p.s sorry for the bad englisch