# Question relations tables **URL:** \ **Category:** Ruby\ **Created:** [October 3, 2007, 12:37pm UTC](https://www.ruby-forum.com/t/question-relations-tables/116203 "2007-10-03T12:37:40Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![remco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/9d8465/32.png) [@remco](https://www.ruby-forum.com/u/remco)\ **Post date:** [October 3, 2007, 12:37pm UTC](https://www.ruby-forum.com/t/question-relations-tables/116203/1 "2007-10-03T12:37:40Z")
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 ---
**Author:** ![remco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/9d8465/32.png) [@remco](https://www.ruby-forum.com/u/remco)\ **Post date:** [October 3, 2007, 1:05pm UTC](https://www.ruby-forum.com/t/question-relations-tables/116203/2 "2007-10-03T13:05:38Z")
> 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/](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 ---
**Author:** ![remco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/9d8465/32.png) [@remco](https://www.ruby-forum.com/u/remco)\ **Post date:** [October 4, 2007, 6:18am UTC](https://www.ruby-forum.com/t/question-relations-tables/116203/3 "2007-10-04T06:18:08Z")
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? ---
**Author:** ![remco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/9d8465/32.png) [@remco](https://www.ruby-forum.com/u/remco)\ **Post date:** [October 4, 2007, 10:15am UTC](https://www.ruby-forum.com/t/question-relations-tables/116203/4 "2007-10-04T10:15:23Z")
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