Please get me some advised…yuor help much be appreciated!!!
Given the table structure and row data below, I have some questions in my mind, sorry Im just a beginner…
mysql> explain user_skill;
±-------------------------±-----------------±-----±----+
| Field | Type | Null | Key |
±-------------------------±-----------------±-----±----+
| user_skill_id | int(11) | | PRI |
| user_skill_last_modified | timestamp(14) | YES | |
| user_skill_date_created | datetime | YES | |
| user_id | int(11) | YES | |
| skill_name | char(255) | YES | |
| skill_level | char(255) | YES | |
| skill_usage | char(255) | YES | |
| skill_last_used | char(255) | YES | |
| user_skill_endorsed | tinyint(1) | YES | |
±-------------------------±-----------------±-----±----+
mysql> *************
±-------------------±------------------±-------------------+
| user_firstname | user_lastname | skill_name |
±-------------------±------------------±-------------------+
| Kim | Simpson | PHP |
| Kim | Simpson | Perl |
| Kim | Simpson | Microsoft Word |
| Kim | Simpson | Microsoft Access |
| Kim | Simpson | Accounting/Billing |
| Kim | Simpson | Java |
| Kim | Simpson | SQL |
| Kim | Simpson | CSS |
| Kim | Simpson | OO Programming |
| Kim | Simpson | Microsoft Excel |
±-------------------±------------------±-------------------+
10 rows in set (0.00 sec)
These are my questions:
1.Assuming that the data stored in skill_name in the user_skill table might be repeated for different users, what changes would I make to the database to normalize the skill_name and reduce repeated storage? what is the structure of the new table(s).
2.How could I Recreate the query that returned the 10 rows of data supplied. Speculate on tables that would be needed that are not shown here.
- Given the following query, how could it be optimized? List all assumptions:
select c.* FROM companies AS c JOIN users AS u USING(companyid) JOIN jobs AS j USING(userid) JOIN useraccounts AS ua USING(userid) WHERE j.jobid = 123;
