[support] Doubt on Index and Primary key

Sat Mar 26 04:41:08 UTC 2011

Hi Dave
I am not sure if I understood your suggestion properly. I am writing below
my understanding.
I have given the table example below, if wrong, please correct me.

Please see my response inline.

Regards
Kamal

On Fri, Mar 25, 2011 at 9:48 PM, Metzler, David <metzlerd at evergreen.edu>wrote:

>  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
>
>
>

So the table will look as below.

   *UID* *PHP* *HTML* *VOIP* *SQL* *MYSQL* 1 1 0 1 0 1 2 0 1 1 1 0 3 1 1 0 1
0 4 0 0 0 0 0

If this is the case, table coulmn will keep increasing.

>  And a lookup table to define skill sets and their categories and their
> relavent work domains:
>
>
>
> Skill_set_id, skill_set_descr, work_domain
>
>
>

Is this example holds good?

    Skill ID Skill desc work domain PHP Good in PHP Web HTML Average in HTML
Web VOIP Average in VoIP VoIP SQL Good in SQL Database MYSQL Good in MySql
Database

>  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 at drupal.org [mailto:support-bounces at drupal.org] *On
> Behalf Of *Austin Einter
> *Sent:* Thursday, March 24, 2011 6:58 PM
> *To:* support at drupal.org; pierre.rineau at makina-corpus.com
>
> *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
>
> Austin.
>
>
>
>
>
>
>
>
>
>
>
> On Fri, Mar 18, 2011 at 8:54 PM, Pierre Rineau <
> pierre.rineau at makina-corpus.com> wrote:
>
> Le vendredi 18 mars 2011 à 08:15 -0700, Metzler, David a écrit :
>
> > Pierre is spot on here.
> >
> > 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.
>
>
> Pierre.
>
>
> --
> [ Drupal support list | http://lists.drupal.org/ ]
>
>
>
> --
> [ Drupal support list | http://lists.drupal.org/ ]
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20110326/79d2b990/attachment-0001.html 