Database design: Multiple of the same type of item -

-

i wasn't sure entirely how word question, i'll explain i'm thinking. have previous experience designing databases haven't decided how want implement or run across before. i'll explain i'm thinking way of showing have thought out...

i'm creating database store information of user things resume (this side project bunch of college kids). issue i'm coming across how deal storing large amount of things technical skills, perhaps of order of 20+ per user proficiency

idea one: 1 huge table columns of techskill1 -> 20. along proficiency. use user id fk relate of these. pros: easiest implement, easiest on front end. cons: limited 20 skills, lots of potential nulls, excessive size

idea two: table of large text input skills in 1 text object delimited character comma or |. column proficiencies delimited same way. again, userid fk. pros: easy implement, small table size, easy on front end information cons: possibility of wasting lot of empty space, need more coding on front end before store , upon retrieving.

idea three: small table column of skill , proficiency. create multiple rows relate each userid pros: smallest table , cleanest. saves space cons: front end implementation interesting in, how deal multiple fields unlimited amount of entries (not stuff, don't want create many issues front end guys)

those 3 ideas, i'm not entirely sure best so... i'm asking guys. advice appreciated.

thanks!

-jabsy

best way implement on database level create 4 tables:

  • users (you have one)
  • skills
  • proficiencies
  • skills_proficiencies (fk_userid, fk_proficiencyid,fk_skill_id)

this way won't waste space , architecture more scalable , maintanable.

you addressed problem of front end implementation. best way remedy create database view (which db engine u use?) more "front end friendly" view of data. it's not idea denormalize schema ease front end development, because data manipulations fragile operations in information systems. keep schema clean , save lot of trouble in future when scaling , adding new features.


Comments

Post a Comment

Popular posts from this blog

linux - Mailx and Gmail nss config dir -

-
i trying send mail linux command line using mailx command. can send local domain no problem want set mail send gmail account receive mail sent gmail account. after configuring mail.rc so: account gmail { set smtp=smtps://smtp.gmail.com:587 set smtp-auth=login set smtp-auth-user=username@gmail.com set smtp-auth-password=password set ssl-verify=ignore } i error: resolving host smtp.gmail.com . . . done. connecting 74.125.25.109 . . . connected. missing "nss-config-dir" variable. "/home/username/dead.letter" 11/354 . . . message not sent. after looking "nss-config-dir" here , located certn.db , keyn.db files , added mail.rc so: account gmail { set smtp=smtps://smtp.gmail.com:587 set smtp-auth=login set smtp-auth-user=username@gmail.com set smtp-auth-password=password set ssl-verify=ignore set nss-config-dir=/home/user/.mozilla/firefox/location.default } now when try send mail using command: echo "sent gmail account" | mailx -v -a
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more