Associating/relating two tables

my_name

Let's say that I have some products, and each product is composed of various parts. Some parts are used in more than one product.

For example, let's say I had these two tables:

PRODUCTS TABLE:

[U][B]ID  | NAME[/B][/U]
001 | Widget 1
002 | Widget 2
003 | Widget 3

PARTS TABLE:

[U][B]ID  | NAME[/B][/U]
00A | Part 1
00B | Part 2
00C | Part 3
00D | Part 4

Let's say that Widget 1 uses Part 1 and Part 2; Widget 2 uses Part 1 and Part 3, and Widget 3 uses Part 1, Part 2, and Part 3.

Is there any way I could make the following two queries?

1.) Select all parts associated with Widget 1. (This should return Part 1 and Part 2 as results.)

2.) Select all products associated with Part 3. (This would return Widget 2 and Widget 3.)

How can I "connect" or "link" these two tables so that I can make these type of queries? And will the queries run bog down the system inordinately? Sorry if this is a newbie-ish question.

Thanks for any guidance!

Jason

Piranha

With those two tables you can't do any of the things you ask. You should add a table in between that connects the tables, just use the id from each of the tables to do this. After that it is pretty straight forward, I think that you will solve it.

By the way, I am a little confused about your way to set ids. If I understand it correctly you use 3 numbers in the first table. It will limit the products to 999, and it is normally not good. More than that, it will be messy to insert a new product. It is more or less the same for the second table. To solve this I suggest that you use an INT that is auto_incremented as the id, then you won't have any problems at all with inserting more records or insert new rows.

Edit: If you still can't do the queries with the extra table please post again.

my_name

Don't worry about the IDs. They're just an example.

How would I create this "in-between" table? What would its structure and data look like? Could you build a sample one using the example tables above?

Piranha

Keep the tables you have and make a new one with the ids from the two others, like this:

table products_parts
products_id references products.id
parts_id references parts.id

Then you insert what product have what part:

insert into table values (001, 00A)
insert into table values (001, 00B)

my_name

That's interesting.

Would the new table be automatically updated if, say, I changed some IDs?

Piranha

IDs should never be changed, the question is not relevant.

I suggest that you do a few tutrials for databases or take a course. Then you will understand the basics and don't have to ask for these kind of things.

my_name

IDs should never be changed? Sure. That sounds nice in theory, but if you've ever been out in the real world and done these kinds of things, you'll see it's really seldom that simple, especially as time passes and things and needs change.

Anyway, thanks for your help.

Sxooter

my_name wrote:
IDs should never be changed? Sure. That sounds nice in theory, but if you've ever been out in the real world and done these kinds of things, you'll see it's really seldom that simple, especially as time passes and things and needs change.

Anyway, thanks for your help.

This conversation is quickly headed towards the rather religious territory of natural and artificial keys.

IF you use artificial keys (i.e. autoinc for both the two main tables) then you can have natural names assigned, and the user will never see the artificial keys. IF you use natural keys, then you have the issue of which you speak.

Me personally, I've found that using artificially generated (and therefore hidden) keys works much better. And artificial keys should never need changing.