Database Design Question

[Tom Tolleson]

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

[darkagn]

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

[Tom Tolleson]

Great idea! I think that does it. Thanks!