Member Avatar for 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

  • 2 Contributors
  • 2 Replies
  • 122 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Tom Tolleson
Member Avatar for 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

Member Avatar for Tom Tolleson

Great idea! I think that does it. Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.