How to structure database for portfolio pieces on profile pages

[Josh Johnson]

http://www.dropbox.com/s/9w1vkhf0pocer58/Josh%20Johnson%20%20%20%20Front%20end%20Developer%20%20%20%20Connectd.png

http://www.dropbox.com/s/fx3uz6u2vpahea8/Harry%20Fox%20%20%20%20Designer%20%20%20%20Connectd.png

I am developing a web application which allows two types of users (Designers and Developers) to upload a portfolio on their profile page (images above).

At the moment all content in the sidebar is dynamic, however the actual portfolio sections are static. I was just wondering how I would structure my database schema to allow portfolio piece uploading?

Both user types will be able to upload up to 6 portfolio images, up to 10 skills, and a testimonial. The only key difference is that Developers can specify how good they are at a certain skill (based on a number 1 to 5, 1 being below average etc)

Thanks

[Alen]

At the moment all content in the sidebar is dynamic

What’s the schema like for this? Presumably you’ll want to add to this database, and associate users with the rest of the data (portfolio, etc)…

[Josh Johnson]

Sure:
Users table:
http://i.imgur.com/aLn1BDB.png

Freelancers table:
http://i.imgur.com/3Bc8jcX.png

User type table:
http://i.imgur.com/dUhSPOb.png

[__]

Both user types will be able to upload up to 6 portfolio images, up to 10 skills, and a testimonial.

for images, you’d need to store (at least) the URL and the id of the user the image belongs to. Optionally, you could also store info about height/width/alt attribute/etc.

For skills: do you choose the skills, or do they?

If you do, then you’ll need a table to list the available skills, and an intersection table to associate user ids with their selected skills and “skill rating” (for designers, you could simply leave this field NULL).

If users are allowed to choose their own skills, you could probably drop the first table and use the intersection table only.

For testimonials, you’ll need to store the text of the testimonial itself, and the id of the user it belongs to. Optional stuff might include publishing date, who authored the testimonial, and so forth.

[Josh Johnson]

Thanks @traq….again haha!

How would I go about fixing duplicate content for skills then? Ideally, I need each skill to be unique for each user id. At the moment this is happening:

http://i.imgur.com/4XbQdCe.png

[__]

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'

[Josh Johnson]

Ahh thanks! Never knew you could do that.

Yes skill_id is the ID of the user – I have been doing this on all my tables. (i.e. testimonial_id references the user_id in the user table) Is that incorrect?

[__]

Is that incorrect?

Not per se. If it’s a consistent practice for you/ your project, that’s good. It was a little bit of a stumbling block to me, as an outsider, but no, it’s not a big deal.

[Josh Johnson]

It was mentioned to me before by someone however didn’t really make much sense. Wasn’t sure what the point was of having columns in my user table with the same value as the user ID.

[Author]

Posts