Database Design Question
Please support our MySQL advertiser: Programming Forums
![]() |
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
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
Last edited by Tom Tolleson : Dec 4th, 2008 at 3:30 pm.
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:
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:
Hope this helps,
darkagn
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:
sql Syntax (Toggle Plain Text)
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:
sql Syntax (Toggle Plain Text)
SELECT * FROM related_widgets WHERE widgetid = 3 OR related_widgetid = 3
Hope this helps,
darkagn
There are no stupid questions, only those too stupid to ask for help.
![]() |
Similar Threads
Other Threads in the MySQL Forum
- newbie question about database design (Computer Science)
- Database Design (Computer Science)
- Database Design Question (Database Design)
- Database Design Question (Database Design)
- your ideas on database design??? (Database Design)
- Cities and Districts DataBase - Basic Question (Database Design)
Other Threads in the MySQL Forum
- Previous Thread: how to update data in mysql table using data from an excel table
- Next Thread: Using the COUNT function with a subquery in SQL
•
•
•
•
Views: 452 | Replies: 2 | Currently Viewing: 1 (0 members and 1 guests)





Linear Mode