Think about how you would write a query to
say who has the skill set x? You can’t really do it, cause you’ve
got to change what table you select based on what you’re looking for?
How much php code will you have to change to a). Add a skill set? B.) add a work
domain?
Rather design a table for user skill sets
with the columns:
Uid, skill_set_id
And a lookup table to define skill sets
and their categories and their relavent work domains:
Skill_set_id, skill_set_descr, work_domain
If you need to create 50 tables for modeling
a concept you probably aren’t normalized. There are other approaches, but
this is probably the simplest.
Dave
From:
support-bounces@drupal.org [mailto:support-bounces@drupal.org] On Behalf Of Austin Einter
Sent: Thursday, March 24, 2011
6:58 PM
To:
Subject: Re: [support] Doubt on
Index and Primary key
Hi Pierre, David, Ursula
Thanks for excellent piece of information. I just went through basic
database concepts like indexing, join, normalisation and tried to analyse how
can I apply these for my job registration site implementation.
After understanding a bit on normalisation and join, I have comeup with
below approach for this specific case.
Instead of having a single table, and comma separated values in table
cells , I am going to split it multiple tables.
I am attaching a table.xls file , please have a look.
In that excel sheet, I have the main table, and I have broken the
main table into 9 different tables. But I hope it need to be broken
into more number of tables, depends on how many different kind of
work domains are there. It may go to 50+ tables.
Examples for work domains are - Web, PSTN, VoIP, NetworkManagement,
GSM, Datbase, BoardDesign etc.
So how many work domains are there, those many tables will be there. In
those tables, a coulmn will represent a particular skill set.
Say under Web Tables, coulmns can be HTML, PHP, Web2.0, Drupal, ASP,
etc
And under NetworkManagement table, NMS and SNMP can be coulmns.
In table cells, I will keep either 1 or 0, depending on the person
has that skill or not.
Example: Lets say User 2 knows NMS and SNMP, User 3 knows only
SNMP, User4 knows only SNMP, then the table will look as below.
|
UID |
NMS |
SNMP |
|
2 |
1 |
1 |
|
3 |
0 |
1 |
|
4 |
0 |
1 |
As first time I am doing this, I might be wrong. If so, kindly
let me know.
Best Regards
On Fri, Mar 18, 2011 at 8:54 PM, Pierre Rineau <pierre.rineau@makina-corpus.com>
wrote:
Le vendredi 18 mars 2011 à 08:15 -0700, Metzler, David a écrit :
>
>
> That is why most dbas would advise against storing this data in a comma
separated list in a single field. An index cannot really be used to
search within the text cause you are forcing to examine every row anyway.
I can't programtically say lets start with the N's, now is there a nokia
in there (that's an oversimplification intentionally to make a point). Rather I
would make a single skill table that housed the values. If UID is the primary
key for the resume, then you'd make a table with
>
> On a separate note, you do understand that the site that you're talking
about building could be done without you writing ANY code? Basically the site
you've described can be implemented with content_profile, cck and views
modules, allowing you to build custom content types that are tied (one per
user). You could then use taxonomys for skill sets an all this would be
written for you?
>
> Dave
Dave is right about the fact this simple business stuff could be done
in
many ways using D6 existing modules (even only with core and taxonomy)
or D7 fields.
But, if you really want to learn technical aspects of SQL and/or Drupal
development, this is a good thing to start with this kind of simple
business stuff.
--
[ Drupal support list | http://lists.drupal.org/
]