Database Design Question

Expand Post »

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

Last edited by Tom Tolleson; Dec 4th, 2008 at 4:30 pm.

Similar Threads

Cities and Districts DataBase - Basic Question in Database Design
newbie question about database design in Computer Science
Database Design in Computer Science
Database Design Question in Database Design
Database Design Question in Database Design

Tom Tolleson

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

39 posts
since Oct 2007

Permalink

Dec 4th, 2008

Re: Database Design Question

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:

sql Syntax (Toggle Plain Text)
INSERT INTO related_widgets (widgetid, related_widgetid) 
VALUES (1, 2)
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
select *  from related_widgets 
where widgetid = 3 or related_widgetid = 3

Hope this helps,
darkagn

darkagn

Reputation Points: 395

Solved Threads: 192

Veteran Poster

Offline

1,136 posts
since Aug 2007

Permalink

Dec 5th, 2008

Re: Database Design Question

Great idea! I think that does it. Thanks!

Tom Tolleson

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

39 posts
since Oct 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: how to update data in mysql table using data from an excel table

Next Thread in MySQL Forum Timeline: Using the COUNT function with a subquery in SQL

DaniWeb Message

Cancel Changes

User Name
Password