0

Hi!

I've got a table of, let's say, widgets. Each has a unique id (primary key) and a few other attributes such as

widgetid (unique, number)
widget_name (string)
widget_price (varchar)
widget_color (string)

BUT, for each widget, I also want to list other widgets (that are listed in the same table) that are similar to each widget, and list them by their unique id.

So, would I create a foreign key to another table of related widgets, or just have additional fields in this table for

related_widget_1
related_widget_2
related_widget_3

and have the widgetids listed in those fields?

Seems their must be a better way to do this.

Thanks!

THT

2
Contributors
2
Replies
3
Views
8 Years
Discussion Span
Last Post by Tom Tolleson
0

I would have another table, related_wigets, with the following structure:

widgetid (number, PK, FK)
related_widgetid (number, PK, FK)

When someone wants to relate widget with widgetid = 1 to widget with widgetid = 2, do an insert like so:

insert into related_widgets (widgetid, related_widgetid) 
values (1, 2)

When you want a full list of which widgets are related to widget with widgetid = 3, the following query will give you a complete list:

select *  from related_widgets 
where widgetid = 3 or related_widgetid = 3

Hope this helps,
darkagn

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.