CODEWITHSUNDEEP
database-design
Jabsy
Jabsy

Reputation: 171

Database design: Multiple of the same type of item

I wasn't sure entirely how to word this for the question, so I'll explain what I'm thinking. I have previous experience designing databases but really haven't decided how I want to implement this or run across this before. I'll explain what I'm thinking as a way of showing I have thought this out...

I'm creating a database that will store information of a user for things like a resume (this is a side project for a bunch of college kids). The issue I'm coming across is how to deal with storing a large amount of things like technical skills, perhaps of the order of 20+ per user as well as their proficiency

Idea one: One huge table with columns of TechSkill1 -> 20. Along with their proficiency. Use the user ID as the FK to relate to all of these. Pros: Easiest implement, easiest on the front end. Cons: limited to 20 skills, lots of potential for nulls, excessive size

Idea two: Table of a large text input with all skills in one text object delimited by some character like a comma or a |. Another column for proficiencies delimited the same way. Again, USERID as a FK. Pros: easy to implement, small table size, easy on the front end to get information Cons: possibility of wasting a lot of empty space, need to do more coding on the front end before the store and then upon retrieving.

Idea Three: Small table with the column of skill and proficiency. Then create multiple rows to relate to each USERID Pros: smallest table and cleanest. Saves the most space Cons: front end implementation will be interesting as in, how to deal with multiple fields for an unlimited amount of entries (not my stuff, but I don't want to create too many issues for the front end guys)

Those were my three ideas, I'm not entirely sure what would be best so... I'm asking you guys. All advice would be greatly appreciated.

Thanks!

-Jabsy

Upvotes: 0

Views: 2040

Answers (4)

Ken Chan
Ken Chan

Reputation: 90427

I will create 3 tables :

  • USER table with the PK USER_ID
  • SKILL table with the PK SKILL_ID , and a column SKILL_DESCRITPION
  • USER_SKILL table with the following structure :
    • USER_ID , FK reference to the USER.USER_ID
    • SKILL_ID , FK reference to the SKILL.SKILL_ID
    • PROFICIENCY

In this way , it can provides you the flexibility to include more different skill type in the future by simply inserting a new skill record in the SKILL table.

For how to query the result about what skills and the proficiency for each user has , you can refer to this http://bytes.com/topic/oracle/answers/64603-help-decode-function-crosstab-query if you are using oracle.Your problem description should be the same as this link described.

Upvotes: 0

Filip Nguyen
Filip Nguyen

Reputation: 1039

best way to implement this on database level is to create 4 tables:

  • USERS (you probably have this one)
  • SKILLS
  • PROFICIENCIES
  • SKILLS_PROFICIENCIES (fk_userid, fk_proficiencyid,fk_skill_id)

This way you won't waste space and your architecture will be more scalable and maintanable.

You addressed problem of front end implementation. The best way remedy this is create database View (which DB engine u use?) with more "front end friendly" view of data. It's not a good idea to denormalize your schema to ease up front end development, mainly because data manipulations are the most fragile operations in information systems. Keep your schema clean and you will save yourself a lot of trouble in the future when scaling up and adding new features.

Upvotes: 2

Justin Cave
Justin Cave

Reputation: 231661

Store the data in a normalized fashion. A USER table with a USER_ID primary key, a SKILL table with a SKILL_ID primary key, and a USER_SKILL table that has a USER_ID, a SKILL_ID, and a PROFICIENCY column.

I'm not sure that I understand whether that is what you're suggesting as the third option. If it is, I'm not quite sure what issues you'd have with the front end. Presumably, there will be an interface where a user adds a new skill and enters their proficiency. That would seem to map very naturally to a properly normalized schema.

Upvotes: 0

Amos
Amos

Reputation: 1060

Here's how I'd do it.

Table 1: Contains a single column with all possible skills listed.

Table 2: Contains three columns, the userID for one column, and an associated skill for the second column. Use a compound key, the second column is limited to entries from the first table. Plus a third column with their level of ability (this might need to reference another table it depends in whether it's just a value or if it's a word). A user might have 20 different entries in thus table.

Upvotes: 0

Related Questions