Message to Bill Wilkinson

1. Ian
Senior Member
Join Date
Dec 1969
Posts
736

Message to Bill Wilkinson

2. Senior Member
Join Date
Dec 1969
Posts
96,118

Ahhh...that opinion applied ONLY...

...to "many-to-many" tables! And there&#039;s no reason to have a PK for a many-to-many table, so that&#039;s why I didn&#039;t worry about it.<BR><BR>So you might delete something out of the combinations table (e.g., "small red sweater" is no longer available), but you&#039;d never delete something from the sizes or colours tables to which they refer! (That is, "small" and "red" would always be there.)<BR><BR>Yes, if a table has a primary key, then I would assume there is a reason for it (i.e., some other table has a FK to it), so you certainly can&#039;t delete and recreate in *that* table.<BR><BR>But that wasn&#039;t the situation I thought you were describing, before; I thought it was just a many-to-many table and I can&#039;t see any reason for *ever* having a PK in such a table.<BR><BR>

3. Ian
Senior Member
Join Date
Dec 1969
Posts
736

Looking up combinations

Thanks for your reply.<BR><BR>Another issue I have with this is how to look up a combination given some option values.<BR><BR>A simple way would be to have a comboID field in the combinations table which would be a string containing the ids of the options which comprised the combination.<BR><BR>So given the option ids 2 and 6 one could just have...<BR><BR>SELECT * FROM combinations WHERE comboID = "2_6"<BR><BR><BR>Alternately, there could be a many to many join table between the table of option values and the combinations table.<BR><BR>Each row would consist of the pk of an option value and the pk of the combination it appears in.<BR><BR>Using this struture, do you know if there&#039;s a simpler way of looking up a combination given some option values than using a recursive self join? For example, this would work for a product with two variations e.g. (colour = 2 and size = 6)<BR><BR>SELECT TOP 1 t1.comboID FROM joinTable t1<BR>JOIN joinTable t2 ON t1.comboID = t2.comboID<BR>WHERE t1.optionID IN ( 2, 6 ) AND t1.optionID &#060;&#062; t2.optionID<BR><BR>I suppose this is obviously less efficient than the first method but I&#039;m still interested in a better way of doing it.<BR><BR>Also, one valuable benifit of having a join table is that deleting an option value could have the system automatically cascade the delete though the combinations table via foreign keys.<BR><BR>Hope this is clear,<BR><BR>I.

4. Ian
Senior Member
Join Date
Dec 1969
Posts
736

Correction

That should be...<BR><BR>SELECT t1.comboID FROM joinTable t1 <BR>JOIN joinTable t2 ON t1.comboID = t2.comboID <BR>WHERE t1.optionID = 2 and t2.optionID = 6

5. Senior Member
Join Date
Dec 1969
Posts
96,118

I wouldn't do that

SELECT colours.color, sizes.size<BR>FROM colours, sizes, colour_sizes<BR>WHERE colours.colorID = colour_sizes.colourID<BR>AND sizes.sizeID = colour_sizes.sizeID<BR>AND colour_sizes.sizeID = 2<BR>AND colour_sizes.colourID = 6<BR><BR>[Do you know how hard it is for me to make my fingers spell "color" wrong???!!!]<BR><BR>What reason is there to have a separate "comboID" when one is easily implied by the two separate conditions on the table???<BR><BR>Agree about the cascade delete idea, though I can&#039;t see a place where it would actually happen in real life if we are really talking about sizes and colours. [You are going to banish the colour "purple" from all products, across all lines, forever? Or you are going to drop all XXL sizes, no matter whether T-shirts or hats?] But you know your own data better than I do.<BR>

6. Ian
Senior Member
Join Date
Dec 1969
Posts
736

Different structure

I&#039;m quite happy to banish the redundant &#039;u&#039; in colour.<BR><BR>There isn&#039;t a seperate table for each type of variation. All the variations - colour, size, nib size etc. - are in the same table. Then, all the different options - small, large, green - are in a different table also and they reference their parent.<BR><BR>The reason for this is that there are many different variations. There are quite a few products with a colour variation but even then, different products have different sets of colours available. <BR><BR>They&#039;ll surely be some redundancy here but I think it outweighs the issue of an admin having to manage several differnet colour variations independantly of the products that they&#039;re assigned to.<BR><BR>So, if the store runs out of &#039;Light Gathering Rods&#039; in colour blue, then only the combinations containing blue for that product will go where as one will still be able to buy a &#039;1.5mm Thick Styrene sheet for vacume forming&#039; in blue.<BR><BR>You wrote..<BR><BR>&#039;I thought it was just a many-to-many table and I can&#039;t see any reason for *ever* having a PK in such a table&#039;<BR><BR>Correct me if I&#039;m wrong but what about this....<BR><BR>There&#039;s a chain of cinemas. Many locations showing many different films. So there&#039;s a join table between the two. Location x showing film y appears in this join table multiple times. Once for each performance date/time.<BR><BR>So why not have a pk for the join table because then one will easily be able to look up a film showing at a particular location and time?<BR><BR>I.

7. Senior Member
Join Date
Dec 1969
Posts
96,118

Okay...I misunderstood from beginning...

...I was assuming a table design quite different than that. <BR><BR>So stick with what works for you.<BR><BR>*************************<BR><BR>There&#039;s a chain of cinemas. Many locations showing many different films. So there&#039;s a join table between the two. Location x showing film y appears in this join table multiple times. Once for each performance date/time.<BR><BR>So why not have a pk for the join table because then one will easily be able to look up a film showing at a particular location and time?<BR><BR>Why do you need the PK? Or if you do have one, why not just make it the composite of the two FKs?<BR><BR>ALL the info you need is already implicit in the pair of FKs.<BR><BR>Ummmm...having said that: This is actually a *THREE* table join! SHOWTIME, THEATRE, and FILM<BR><BR>Yeah, indexing the three fields in the connecting table makes sense, but I don&#039;t see a reason to make a PK out of them. But if you do, just make it the combo of the three FKs, presuming that would be unique and thus eligible to be the PK. Why have a separate field that is nothing but the combo of the three???<BR><BR><BR>

8. Ian
Senior Member
Join Date
Dec 1969
Posts
736

RE: Okay...I misunderstood from beginning...

That sounds quite convincing.<BR><BR>Returning to looking up a product combination...<BR><BR>Is it possible, using SQL Server, to select a group of records and then return only those which share a value in one column - something like this...<BR><BR>SELECT * FROM table1 WHERE (pk &#062; 20 AND pk &#060; 30) AND column1 EQUAL<BR><BR>I get the impression that the only way to compare records to each other within a query is to use a join or a sub query.<BR><BR>Thanks for your help.<BR><BR>I.

9. Senior Member
Join Date
Dec 1969
Posts
96,118

JOIN to self...

SELECT * FROM table1 AS T1, table1 AS T2<BR>WHERE T1.pk &#062; 20 AND T1.pk &#060; 30<BR> AND T2.pk &#062; 20 AND T2.pk &#060; 30<BR>AND T1.column1 = T2.column1<BR><BR>Not sure from the way you asked whether the stuff in italics is needed/desired.<BR><BR>

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•