[support] Doubt on Index and Primary key

[Austin Einter]

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/ ]
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20110325/dc038e74/attachment-0001.html 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Table.xls
Type: application/vnd.ms-excel
Size: 41984 bytes
Desc: not available
Url : http://lists.drupal.org/pipermail/support/attachments/20110325/dc038e74/attachment-0001.xls