<!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">
<font color="#333399">Austin,<br>
<br>
I haven't dealt with high row counts with MySQL - I don't know for
sure. But the rows are small so you're not using too much space. I
would think it should be OK with some performance tuning. I'd
suggest indexing the Skillset table by UID and sID.That and maybe
some decent hardware configuration should be able to handle it.<br>
<br>
Any comments from the MySQL people?<br>
<br>
Regards,<br>
<br>
Pat<br>
</font><br>
On 3/25/2011 7:24 PM, Austin Einter wrote:
<blockquote
cite="mid:AANLkTim3p=MZuFGwtKp6ub9huaM2p7_AOcWBbrZfeUky@mail.gmail.com"
type="cite">
<div>Hi Pat</div>
<div>Thanks for detail help. I really appreciate it. I did put the
tables in excel sheet. It looks fine.</div>
<div> </div>
<div>But the only worry is skillset table row count.</div>
<div> </div>
<div>Assuming on average, one person has 10 different skills, then
skillset table will have 10 entries per user.</div>
<div>Assuming total 50,000,00 users (May be a higher number , but
I want to design keeping this number high), then total number of
rows in skillset table will be 50,000,00 x 10 = 50,000,000 which
is really high. Is this going to be a bottle neck from MySql
perspective, search time perspective?? </div>
<div> </div>
<div>I am attaching the excel sheet (please where I have put the
tables as per suggestion), please have a look.</div>
<div> </div>
<div>Best Regards</div>
<div>Austin<br>
<br>
</div>
<div class="gmail_quote">On Fri, Mar 25, 2011 at 2:27 PM, Pat
Johnston <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:Pat@melrosecenter.com">Pat@melrosecenter.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">
<div text="#000000" bgcolor="#ffffff">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>
<font color="#888888"><br>
Pat</font>
<div>
<div class="h5"><br>
<br>
On 3/24/2011 6:58 PM, Austin Einter wrote:
<blockquote 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="min-height: 16.5pt;" height="22">
<td style="border-width: 1.5pt medium 1.5pt
1.5pt; border-style: solid none solid solid;
border-color: -moz-use-text-color;
background-color: rgb(219, 229, 241);
min-height: 16.5pt; width: 95pt;"
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:
-moz-use-text-color; background-color:
rgb(219, 229, 241); width: 64pt;" 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: -moz-use-text-color;
background-color: rgb(219, 229, 241); width:
64pt;" width="85"><font color="#ff0000"
face="Calibri">SNMP</font></td>
</tr>
<tr style="min-height: 16.5pt;" height="22">
<td style="border-width: medium medium medium
1.5pt; border-style: none none none solid;
border-color: -moz-use-text-color;
background-color: transparent; min-height:
16.5pt; width: 95pt;" height="22"
width="126"><font face="Calibri">2</font></td>
<td style="border: medium none;
background-color: transparent; width: 64pt;"
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: -moz-use-text-color;
background-color: transparent; width: 64pt;"
width="85"><font face="Calibri">1</font></td>
</tr>
<tr style="min-height: 15.75pt;" height="21">
<td style="border-width: medium medium medium
1.5pt; border-style: none none none solid;
border-color: -moz-use-text-color;
background-color: transparent; min-height:
15.75pt; width: 95pt;" height="21"
width="126"><font face="Calibri">3</font></td>
<td style="border: medium none rgb(236, 233,
216); background-color: transparent; width:
64pt;" 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: -moz-use-text-color;
background-color: transparent; width: 64pt;"
width="85"><font face="Calibri">1</font></td>
</tr>
<tr style="min-height: 15.75pt;" height="21">
<td style="border-width: medium medium 1.5pt
1.5pt; border-style: none none solid solid;
border-color: -moz-use-text-color;
background-color: transparent; min-height:
15.75pt; width: 95pt;" 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:
-moz-use-text-color; background-color:
transparent; width: 64pt;" 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: -moz-use-text-color;
background-color: transparent; width: 64pt;"
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"
target="_blank">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>> 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><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>
</div>
</div>
</div>
<br>
--<br>
[ Drupal support list | <a moz-do-not-send="true"
href="http://lists.drupal.org/" target="_blank">http://lists.drupal.org/</a>
]<br>
</blockquote>
</div>
<br>
</blockquote>
<br>
</body>
</html>