Forums

The forums ran from 2008-2020 and are now closed and viewable here as an archive.

Home Forums Back End How to structure database for portfolio pieces on profile pages Reply To: How to structure database for portfolio pieces on profile pages

#169167
__
Participant

I need each skill to be unique for each user id

is skill_id the id of the user? (I wouldn’t think so, but that’s what your example makes it look like.)

Anyway, whatever the user id is, make it a compound primary key along with the skill. So, something like:

create table user_skills(
    user_id bigint unsigned not null,
    skill varchar(50) not null,
    skill_rating int default null,
    primary key( user_id,skill ),
    foreign key( user_id ) references user( id )
)engine=innodb;

Then,

insert into user_skills (user_id,skill)
values( 1,"HTML" ),
-- okay
( 1,"CSS" ),
-- okay
( 2,"HTML" ),
-- okay
( 1,"HTML" )
-- ERROR 1062 (23000): Duplicate entry '1-HTML' for key 'PRIMARY'