December 6, 2004 at 10:53 am
Hi,
I just designed a many-to-many relational database that goes a bit like this:
Artists table:
artistID
firstName
lastName
some other fields
Products table:
productID
name
price
Since each artist can have many products, and each product can have many artists, it's many-to-many and so I have the relational table:
ArtistsProducts table:
artistsProductsID (primary key)
artistID
productID
Typical many-to-many and it works, but it's my first time with it. I need to query this database to retrieve all artists and their associated products in one query set, so I made this query and it works, but I want to know WHY it works (I kind of guessed at it and can't figure this out):
SELECT artists.artistID,
artists.firstName,
artists.lastName,
products.productID,
products.name,
products.price
FROM artists,products,artistsProducts
WHERE (artists.artistID = artistsProducts.artistID) AND (products.productID = artistsProducts.productID)
Please explain so that I may learn something from this, or in case I didn't do this right, fix it.
Thanks,
Dave
December 6, 2004 at 11:06 am
Small fix (maybe it'll clear it up too 🙂 ) :
SELECT artists.artistID,
artists.firstName,
artists.lastName,
products.productID,
products.name,
products.price
FROM artists inner join artistsProducts
on artists.artistID = artistsProducts.productID inner join products on artistsProducts.artistID = products.productID
basically you are asking sql server to first match the Artists to the artistsproducts with a matching id, then to match this first part of the results set to the products table (again with matching id).
btw a join written like this is not an 'standard accepted syntaxe'... but I guess it's always gonna work
FROM artists,products,artistsProducts
WHERE (artists.artistID = artistsProducts.artistID) AND (products.productID = artistsProducts.productID)
December 6, 2004 at 11:15 am
Thanks, I'm starting to follow you here, and I appreciate the move toward better syntax, but did you make a typo?
You said:
FROM artists inner join artistsProducts
on artists.artistID = artistsProducts.productID inner join products on artistsProducts.artistID = products.productID
Should that not be?:
FROM artists inner join artistsProducts
on artists.artistID = artistsProducts.artistID inner join products on artistsProducts.productID = products.productID
thanks again for clarifying more,
Dave
December 6, 2004 at 11:17 am
Of course it should be what you said... must mean you're catching on.
December 6, 2004 at 11:24 am
Hehe, nice curveball there, I'm on my toes now... thanks, Remi!!
- Dave
December 6, 2004 at 11:27 am
Even I don't like to be wrong 🙂
December 6, 2004 at 12:02 pm
your primary key should be artistID/productID (a composite primary key).
if not, a unique constraint you have on those two columns, I hope!
December 6, 2004 at 12:13 pm
Thanks Master Yoda!
In the artists table, artistID is the primary key and of course that's unique. In the products table, productID is the primary key, unique as well. Then in the artistsProducts table, artistsProductsID is its own primary key.
The artistsProductsID is just not really used since that table just relates the artists table to the products table. It is always unique, though. Instead of an autonumber field, would it be better to make artistsProductsID a concatenation of artistID and productID? Seems the same either way to me, but please advise if I've gone down a bad design path. This is my first many-to-many foray.
I think I can see what you're saying but I'm having trouble wrapping my head around it and how it fits with my application. Your concern is that the same combination of artistID and productID could occur in the artistsProducts table? That sounds sticky indeed, and I imagine it would introduce problems. Advice? FYI, I'm doing this in a MySQL environment.
- Dave
December 6, 2004 at 12:22 pm
Here's what I usually do...
if I know I'm gonna use the NN table in another relation (can't think on one in this situation at the moment), then I use an identity field as the primary key, however if it's not the case then the new column has not utility and is simply causing overhead on the server everytime you query that table.
The new identity column comes in handy if you have relations to that nn table because you don't have to use a composite foreign keys (which means you don't have to repeat the data).
December 6, 2004 at 12:33 pm
Thanks again!
I'm almost un-confused, but not quite (bear with me, please):
I'm not going to need to relate this NN table to anything else, so yes, I agree that the identity field artistsProductsID is totally pointless, and I put it there simply out of habit -- I've never designed a table without an identity or primary key field -- is it okay to do that?
Should this table simply be two columns?:
ArtistID
ProductID
Do I have to take any further action (like somehow linking these two columns in the database -- dunno how in mysql)? (My app does this well though already.)
thanks,
Dave
December 6, 2004 at 12:44 pm
>>Do I have to take any further action (like somehow linking these two columns in the database -- dunno how in mysql)? (My app does this well though already.)
hmm ... yes.
For MySQL, Yoda is not sure of features. Not a tool of a jedi, MySQL is, but sometimes, make best of what we can, we must, hm?
Yes, a 2-column table is fine. Often you will see this, for a many-to-many relation.
Two tools you must use, protect you they will from the dark side:
1) create composite primary key on the two columns.
2) declare a foreign key constraint on each column to the tables from which the ID's are related
Should further info be required to store with this combination (artistID/productID), add columns to this table you can.
Yoda, personally, does not add identity columns as primary keys unless absolutely unavoidable. Some tables, yoda may have a 2 or 3 column PK. Many database programmers, aware of this possibility they are not!
Adding ID's to every table... hmm! A direct path to the dark side, of this you must beware! Wise you are, to come here for assistance.
December 6, 2004 at 1:04 pm
Wise you are, Master Yoda!
In mysql, I used:
ALTER TABLE `artistsProducts` DROP `artistProductID`
ALTER TABLE `artistsProducts` ADD PRIMARY KEY (artistID,productID)
And viola! That is now my primary key. Thank you very much! I search high and low on the web, but couldn't find answers, and here I got lots of help. Cheers!
While when playing Star Wars Knights of the Old Republic, I am Sith, when programming I aspire to the light.
(If a gamer Yoda be, Knights of the Old Republic 2: The Sith Lords come out today it does! Young Jedi Dave first must finish freelance work, sadly.)
- Dave
December 6, 2004 at 1:07 pm
Hey Yoda I'd be interested in hearing your thaught process on how to pick your PKS and when you use identity (as absolutely necessary) and when to avoid it at all costs.
December 7, 2004 at 7:33 am
hm...
If natural key arises from data, use that you should! A table of US States ... abbreviation, a good key makes, not a "StateID" identity, hm? Too may times, Yoda has seen this!
Many-to-many table, by definition, the key is defined. No identity to add. Even related tables to that many-to-many relationship, surely they also relate to the original entities as well, hmm?
Transaction tables, occassionally, are candidate for an identity ... or data over which you have no control and need to be validated (temp tables for imports) ...
Most often, Yoda prefers short abreviated names or 3 character codes for items like Divisions or companies or Regions, things like that.
Employees, Customers, etc -- Yoda prefers to obtain primary key from business process. Often EmployeeID or CustomerID are meaningful in many systems, and/or may need to be character-based. Allow a business process to assign these codes, not a random identity. Database should receive the key for these entities, not create them. That is just a preference there, of course -- a matter of opinion.
Too often, only constraint on table is an identity column also serving as the primary key. Not much of a constraint this is, hm? Not too constraining, indeed! Allows any rows to be added, it does!
December 7, 2004 at 7:55 pm
I personally use identity columns a lot, as integers perform much better than char or varchar columns, especially when joining tables. However, this can cause performance problems when looking a value up by its code instead of its ID. So, Yoda's advice is not wrong (for example, I have a state table who's clustered index is the state abreviation, and a similar one for country).
I'd also add that PK constraints serve two main purposes, data integrity and query optimization (if clustered). If you check the execution plan for the query that joins these three tables, once with the PK on ArtistProductID and again with the PK as (ArtistID, ProductID), you'll see an incredible performance increase, especially in large tables when you're looking for one specific record or a sequential range of records.
good luck, and test test test.
Signature is NULL
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply