| Author |
Topic |
|
jhermiz
3564 Posts |
 Posted - 2004-08-30 : 17:08:27
|
| I have a table ok a few tables:Client------ClientID (PK)Client (varchar).......FunctionalGroup---------------FunctionalGroupID (PK)FunctionalGroup (varchar)....Manager--------ManagerID (PK)Manager (varchar)....So basically a client is one of our divisions or sister companies.Each client has its own set of functional groups like (Engineering, Purchasing, etc)So I created an intermediate table:ClientFunctionalGroups----------------------ClientIDFunctionalGroupIDManagerID..........My question is I have no need for a primary key in this table.So is it best to index on all 3 of these fields ?Thanks,JonJonwww.web-impulse.comCan you dig it: http://www.thecenturoncompany.com/jhermiz/blog/ |
|
|
DustinMichaels
Constraint Violating Yak Guru
464 Posts |
Posted - 2004-08-30 : 17:17:32
|
| Couldn't you make the 3 columns ClientID, FunctionalGroupID, ManagerID a primary key. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-30 : 17:19:56
|
| Jon,There is always a need for a primary key (some exclusions, but not here). The primary key would cover all 3 columns. You will get an index with it. It should be clustered.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-08-30 : 21:25:26
|
quote:
Originally posted by tduggan
Jon,There is always a need for a primary key (some exclusions, but not here). The primary key would cover all 3 columns. You will get an index with it. It should be clustered.Tara
The problem here is...some functional groups do not have a manager.So If I include all three this requires that a value be placed in ManagerID. I want to allow nulls in this field.So I take it I should cluster the ClientID and FunctionalGroupID then ?Thanks T Jonwww.web-impulse.comCan you dig it: http://www.thecenturoncompany.com/jhermiz/blog/ |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-30 : 22:45:26
|
quote:
Originally posted by jhermizThe problem here is...some functional groups do not have a manager.So If I include all three this requires that a value be placed in ManagerID. I want to allow nulls in this field.
Just tell the two Bobs to 'fix the glitch'. That way the whole problem will work itself out naturally.:)-ec |
|
|
|
timmy
Master Smack Fu Yak Hacker
1242 Posts |
Posted - 2004-08-30 : 23:31:21
|
quote:
Just tell the two Bobs to 'fix the glitch'. That way the whole problem will work itself out naturally.:)
"We find it's always best to avoid confrontation whereever possible....." |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-30 : 23:47:29
|
Why don't you put an identity column on there called ClientFunctionalGroupsID and put the primary key on there?  j/kUse a unique, clustered index and allow NULLs on the ManagerID. I disagree that this table needs a primary key (rare). If you do ever decide to join this structure to something, you need to put an identity column on there and just use that so you don't have to carry all three keys around. You could create that now with a nonclustered primary key just for kicks and grins.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-08-31 : 08:42:34
|
| Hmm...So what's the answer here .. I'm still kind of puzzled as to where to go? Create a PK ClientFunctionalGroupID ?Or just use those other two FunctionalGroupID and ClientID as keys ?Jonwww.web-impulse.comCan you dig it: http://www.thecenturoncompany.com/jhermiz/blog/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-31 : 12:16:45
|
| I disagree about putting an identity column in that table. What's wrong with carrying those 3 columns into another table for a foreign key relationship?Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-08-31 : 12:17:51
|
| i just indexed those fields..didnt create a PKJonwww.web-impulse.comCan you dig it: http://www.thecenturoncompany.com/jhermiz/blog/ |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-31 : 22:45:43
|
| Write a few really, really complex and long joins on that and get back with me Tara. :) Also, let me know how much better that compound index performs and how much bigger the table is when you have 20 million records.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-01 : 11:59:18
|
| I still don't think it'll matter with 3 columns all of which are int.Tara |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-09-01 : 12:19:44
|
quote:
Originally posted by derrickleggett
Write a few really, really complex and long joins on that and get back with me Tara. :) Also, let me know how much better that compound index performs and how much bigger the table is when you have 20 million records.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA.
Well...you will have less joins required when you don't use surrogates...And as for the size...you still need the columns anyway...whether it's in the base table or a "code" table...Which all isn't really the discussion here...but that's where it always ends up....Derrick...can I have a hit?Brett8-) |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-09-01 : 12:25:51
|
| Err I didn't create an identity...so lets not get all egotistical on each other hahahaha...All I ended up doing was indexing those fields. I don't think I'd ever need to carry an identity in this table (only in my case though, only cause our particular system wont need it). But I guess if one were added it wouldn't hurt...if you needed it.Jonwww.web-impulse.comCan you dig it: http://www.thecenturoncompany.com/jhermiz/blog/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-01 : 12:42:11
|
| We know you didn't, just discussing if you did though. On a conjoint table (is that what they are called, or is it junction), I just don't seen the need for one.Tara |
|
|
|
jhermiz
3564 Posts |
Posted - 2004-09-01 : 12:44:47
|
| Okie dokie.Conjoint is bringing in the two entities together so i think you are right.Jonwww.web-impulse.comCan you dig it: http://www.thecenturoncompany.com/jhermiz/blog/ |
|
|
|
|
indexing..