<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Austin,<br>
<br>
I think you're off to a good start. I might suggest that you do
something about all of those skill-set tables. You could replace
them with three tables and still have the information in easy reach:<br>
<br>
- A skillGroup table with<br>
-- sgID - an integer primary key<br>
-- name - name of the group, like 'Web Skills', 'Database'<br>
<br>
- A skills table with<br>
-- sID - an integer primary key<br>
-- sgID - ID of the skillGroup that the skill belongs to<br>
-- skill - the name of the skill, like 'PHP' or 'PostgreSql'<br>
<br>
- A skillset table with<br>
-- UID - your user ID<br>
-- sID - ID of the skill being rated<br>
-- skillRating - 0 or 1 or whatever<br>
<br>
The skillset entry links to the skill via the sID and to the user
table (Table 1) with the UID. The skill table is then linked to the
skillGroup via the sgID. This way you can add skill groups and
skills as need without needing to create more tables.<br>
<br>
Good Luck,<br>
<br>
Pat<br>
<br>
On 3/24/2011 6:58 PM, Austin Einter wrote:
<blockquote
cite="mid:AANLkTi=_7kXCvAohvhmXaLeHMvZhrZ7bE=fdPrEsA_Cd@mail.gmail.com"
type="cite">
<div>Hi Pierre, David, Ursula</div>
<div>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.</div>
<div> </div>
<div>After understanding a bit on normalisation and join, I have
comeup with below approach for this specific case.</div>
<div> </div>
<div>Instead of having a single table, and comma separated values
in table cells , I am going to split it multiple tables.</div>
<div>I am attaching a table.xls file , please have a look. </div>
<div> </div>
<div>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.</div>
<div> </div>
<div>Examples for work domains are - Web, PSTN, VoIP,
NetworkManagement, GSM, Datbase, BoardDesign etc.</div>
<div> </div>
<div>So how many work domains are there, those many tables will be
there. In those tables, a coulmn will represent a particular
skill set.</div>
<div> </div>
<div>Say under Web Tables, coulmns can be HTML, PHP, Web2.0,
Drupal, ASP, etc</div>
<div>And under NetworkManagement table, NMS and SNMP can be
coulmns.</div>
<div> </div>
<div>In table cells, I will keep either 1 or 0, depending on the
person has that skill or not.</div>
<div> </div>
<div> </div>
<div> 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.</div>
<div> </div>
<div>
<table style="width: 223pt; border-collapse: collapse;"
border="0" cellpadding="0" cellspacing="0" width="296">
<colgroup>
<col style="width: 95pt;" width="126">
<col style="width: 64pt;" span="2" width="85">
</colgroup><tbody>
<tr style="height: 16.5pt;" height="22">
<td style="border-width: 1.5pt medium 1.5pt 1.5pt;
border-style: solid none solid solid; border-color:
rgb(23, 55, 93) rgb(236, 233, 216) rgb(23, 55, 93)
rgb(23, 55, 93); background-color: rgb(219, 229, 241);
width: 95pt; height: 16.5pt;" class="xl73" height="22"
width="126">
<strong><font color="#ff0000" face="Calibri">UID</font></strong></td>
<td style="border-width: 1.5pt medium; border-style: solid
none; border-color: rgb(23, 55, 93) rgb(236, 233, 216);
background-color: rgb(219, 229, 241); width: 64pt;"
class="xl74" width="85"><font color="#ff0000"
face="Calibri">NMS</font></td>
<td style="border-width: 1.5pt 1.5pt 1.5pt medium;
border-style: solid solid solid none; border-color:
rgb(23, 55, 93) rgb(23, 55, 93) rgb(23, 55, 93) rgb(236,
233, 216); background-color: rgb(219, 229, 241); width:
64pt;" class="xl75" width="85"><font color="#ff0000"
face="Calibri">SNMP</font></td>
</tr>
<tr style="height: 16.5pt;" height="22">
<td style="border-width: medium medium medium 1.5pt;
border-style: none none none solid; border-color:
rgb(23, 55, 93) rgb(236, 233, 216) rgb(236, 233, 216)
rgb(23, 55, 93); background-color: transparent; width:
95pt; height: 16.5pt;" class="xl76" height="22"
width="126"><font face="Calibri">2</font></td>
<td style="border-width: medium; border-style: none;
border-color: rgb(23, 55, 93) rgb(236, 233, 216)
rgb(236, 233, 216); background-color: transparent;
width: 64pt;" class="xl70" width="85"><font
face="Calibri">1</font></td>
<td style="border-width: medium 1.5pt medium medium;
border-style: none solid none none; border-color:
rgb(23, 55, 93) rgb(23, 55, 93) rgb(236, 233, 216)
rgb(236, 233, 216); background-color: transparent;
width: 64pt;" class="xl71" width="85"><font
face="Calibri">1</font></td>
</tr>
<tr style="height: 15.75pt;" height="21">
<td style="border-width: medium medium medium 1.5pt;
border-style: none none none solid; border-color:
rgb(236, 233, 216) rgb(236, 233, 216) rgb(236, 233, 216)
rgb(23, 55, 93); background-color: transparent; width:
95pt; height: 15.75pt;" class="xl66" height="21"
width="126"><font face="Calibri">3</font></td>
<td style="border: medium none rgb(236, 233, 216);
background-color: transparent; width: 64pt;"
class="xl65" width="85"><font face="Calibri">0</font></td>
<td style="border-width: medium 1.5pt medium medium;
border-style: none solid none none; border-color:
rgb(236, 233, 216) rgb(23, 55, 93) rgb(236, 233, 216)
rgb(236, 233, 216); background-color: transparent;
width: 64pt;" class="xl67" width="85"><font
face="Calibri">1</font></td>
</tr>
<tr style="height: 15.75pt;" height="21">
<td style="border-width: medium medium 1.5pt 1.5pt;
border-style: none none solid solid; border-color:
rgb(236, 233, 216) rgb(236, 233, 216) rgb(23, 55, 93)
rgb(23, 55, 93); background-color: transparent; width:
95pt; height: 15.75pt;" class="xl68" height="21"
width="126">
<font face="Calibri">4</font></td>
<td style="border-width: medium medium 1.5pt;
border-style: none none solid; border-color: rgb(236,
233, 216) rgb(236, 233, 216) rgb(23, 55, 93);
background-color: transparent; width: 64pt;"
class="xl72" width="85"><font face="Calibri">0</font></td>
<td style="border-width: medium 1.5pt 1.5pt medium;
border-style: none solid solid none; border-color:
rgb(236, 233, 216) rgb(23, 55, 93) rgb(23, 55, 93)
rgb(236, 233, 216); background-color: transparent;
width: 64pt;" class="xl69" width="85"><font
face="Calibri">1</font></td>
</tr>
</tbody>
</table>
</div>
<div> </div>
<div> </div>
<div>As first time I am doing this, I might be wrong. If so,
kindly let me know.</div>
<div> </div>
<div>Best Regards</div>
<div>Austin.</div>
<div> </div>
<div> </div>
<div> </div>
<div><br>
<br>
</div>
<div class="gmail_quote">On Fri, Mar 18, 2011 at 8:54 PM, Pierre
Rineau <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:pierre.rineau@makina-corpus.com">pierre.rineau@makina-corpus.com</a>></span>
wrote:<br>
<blockquote style="border-left: 1px solid rgb(204, 204, 204);
margin: 0px 0px 0px 0.8ex; padding-left: 1ex;"
class="gmail_quote">Le vendredi 18 mars 2011 à 08:15 -0700,
Metzler, David a écrit :<br>
<div class="im">> Pierre is spot on here.<br>
><br>
> 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<br>
><br>
> 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?<br>
><br>
> Dave<br>
<br>
</div>
Dave is right about the fact this simple business stuff could
be done in<br>
many ways using D6 existing modules (even only with core and
taxonomy)<br>
or D7 fields.<br>
<br>
But, if you really want to learn technical aspects of SQL
and/or Drupal<br>
development, this is a good thing to start with this kind of
simple<br>
business stuff.<br>
<div>
<div class="h5"><br>
Pierre.<br>
<br>
<br>
--<br>
[ Drupal support list | <a moz-do-not-send="true"
href="http://lists.drupal.org/" target="_blank">http://lists.drupal.org/</a>
]</div>
</div>
</blockquote>
</div>
<br>
</blockquote>
<br>
</body>
</html>