MySQL Forums :: General :: understanding the best design method for links table


Advanced Search

understanding the best design method for links table
Posted by: Jason Bronson ()
Date: December 31, 2010 08:35PM

I've got a table which has items in it for an autocomplete list when users type in a name it populates a list.

CREATE TABLE `items` (
`id` double NOT NULL auto_increment,
`name` varchar(2000) NOT NULL,
`description` text NOT NULL,
PRIMARY KEY USING BTREE (`illness_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1


I'm trying to create another table which will have links to combine the items so when a user search's with text, they get a list of items related but not always exactly what they type....

Example

User types in "ap"
SQL grabs the item from the items table using a
"select id from items where name like 'ap%'"

Returns let's say
apple, apricot, artichokes

I would then need to pull from my new table not created yet the links which apple has to other items in the table, because I might want to offer to the user oranges if they are using apples and apricot might link to apples and artichokes etc...

In theory there could be many relationships between the items...
Obviously I could easily just have a simple link table like this but I wondered what other options there are?

CREATE TABLE `links` (
`id` int(11) NOT NULL auto_increment,
`item_id` double NOT NULL,
`item_link` double NOT NULL,
PRIMARY KEY (`id`)
)

Options: ReplyQuote


Subject Written By Posted
understanding the best design method for links table Jason Bronson 12/31/2010 08:35PM
Re: understanding the best design method for links table Rick James 01/02/2011 03:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.