[Resolved] Database design advice

[saracen]

Hi,

Im creating a site where users can login and create a skills profile. Each user has their own db entry with all the usual username,password, last logged in etc.

What I am not sure about is the skills profile part. They will be selecting their skills from a javascript list after selecting each skill they will be also adding the no. of years experience in that skill.

So, I would love some advice on how best to enter these variables into my db and Joining the info to the user. Keep in mind that the no. of skills can vary wildly from user to user.

Is it a case of justing putting all the skills in one column and the years in another and hoping they match up when listed. Or is there some simple JOIN function that would work efficiently here?

Have any of you guys or gals have any advice or experience in anything similar

[petitduc]

You should get some exposure (take some course, read a book...) to database modelling. Really. SQL alone isn't enough.

What you have here is many-to-many relationship between the entity "user" and the entity "skill".
The usual way to deal with such a case is to have 3 tables :
a table for entity 1
another for entity 2
an association table

You don't put any information about skills in the user table. No information about users in the skills table either.

It is only in the association table that this information is held.
In this table each row consists of :
a user ID (foreign key, the primary key of the users table)
a skill ID (same from skills table)
eventually years of experience

the combination of user id and skill id make up the primary key (a composite primary key)

For example :

userID skillID years

1 3 1
1 2 2
4 3 2
4 1 5
4 5 3
2 6 3

You could create the table by for example :

CREATE TABLE userskill
(
userID INT NOT NULL REFERENCES users(id),
skillID INT NOT NULL REFERENCES skills(id),
years INT,
PRIMARY KEY(userID,skillID)
);

to retrieve all the skills of a particular user you join this table with the users table

hope it helps you

[saracen]

Originally posted by petitduc
You should get some exposure (take some course, read a book...) to database modelling. Really. SQL alone isn't enough.

Couldnt agree with you more!!🙂 Thanks for the reply. I had thought about the third table but I didnt know about being able to create composite primary keys. Thats definitely the answer though. Many many thanks.