- This topic is empty.
April 28, 2014 at 2:37 am #168772
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)
ThanksApril 28, 2014 at 12:53 pm #168798AlenParticipant
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)…April 28, 2014 at 2:07 pm #168801April 28, 2014 at 7:12 pm #168817
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.May 3, 2014 at 2:24 pm #169162May 3, 2014 at 7:02 pm #169167
I need each skill to be unique for each user id
skill_idthe 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;
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'May 4, 2014 at 2:22 am #169180
Ahh thanks! Never knew you could do that.
skill_idis the ID of the user – I have been doing this on all my tables. (i.e.
user_idin the user table) Is that incorrect?May 4, 2014 at 2:11 pm #169198
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.May 4, 2014 at 2:36 pm #169203
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.
- The forum ‘Back End’ is closed to new topics and replies.