Question relations tables

Ruby
1

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

2

I have a user-table described as below. That is filled in trough the
| email | varchar(128) | NO | | |
|
ect.
Posted via http://www.ruby-forum.com/.

The answer depends on in what form that data is captured. If you’re
capturing a single value (such as the amount of years for work
experience,
or a single subject as a major for college/university degrees), you can
probably get away with just adding more columns.
If you’re capturing past employers with years worked there and the
addresses, and a list of programming languages an individual is
proficient
in, it might make more sense to make more tables and link back to the
individual via its id.

That database layout looks like MySQL, so I’ll assume you’re using that.
To
add a table, you can either simply open a command line tool session to
the
database, logging in as a user that has sufficient rights to create
tables.
Then simply run:
create table workexperience(

);

You can also use GUI oriented tools, such as MySQL Administrator
(available
for free from the MySQL website), or phpMyAdmin, a php frontend to MySQL
databases. Lastly, if you’re using Rails, look into database migrations.

HTH,

Felix

3

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?

4

Ron F. wrote:

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