[SOLVED] DB design question

[webweever]

I currentyl have 2 table:

thing = thing_id, name, street, city, state, zip, etc...

category = cat_id, cat_name

 

When I created the tables I didnt think about the fact that each thing could belong to multiple categories. My question is how can I change this thing so that I can have a thing that belongs to more than one category. Currently I'm displaying results by SELECTing name, street from thing where cat_name = whatever.

 

Any ideas?

 

[corbin]

You could have a linking table.

 

 

 

thing_categories

thing_id, cat_id

[webweever]

How would I do that in MySQL? I can obviously create the table but all I need to do is label the thing_id and the cat_id as Foreign Keys?

[kickstart]

Hi

 

Strictly speaking you wouldn't even need to have them as foreign keys (recommended though).

 

However as an example, assuming the table structures above the following would give you all the categories an item belongs to.

 

SELECT a.name, c.cat_name

FROM thing a

JOIN thing_categories b ON a.thing_id = b.thing_id

JOIN category c ON b.cat_id = c.cat_id

 

All the best

 

Keith

[webweever]

This is my DB, considering what you've said.  Is this normalized?

[webweever]

Below is how my DB is setup but I can't get any results with this SELECT statement (SELECT * FROM markers JOIN marker_category ON markers.marker_id = marker_category.marker_id JOIN category ON marker_category.cat_id = category.cat_id)

 

CREATE TABLE IF NOT EXISTS `category` (
  `cat_id` int(11) NOT NULL AUTO_INCREMENT,
  `cat_name` varchar(100) NOT NULL,
  PRIMARY KEY (`cat_id`),
  UNIQUE KEY `cat_id` (`cat_id`,`cat_name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE IF NOT EXISTS `markers` (
  `marker_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `street` varchar(200) NOT NULL,
  `city` varchar(100) NOT NULL,
  `state` varchar(100) NOT NULL,
  `zip` int(11) NOT NULL,
  `url` varchar(200) NOT NULL,
  `lat` float(10,6) NOT NULL,
  `lng` float(10,6) NOT NULL,
  PRIMARY KEY (`marker_id`),
  UNIQUE KEY `marker_id` (`marker_id`,`url`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=29 ;

CREATE TABLE IF NOT EXISTS `marker_category` (
  `marker_id` int(11) NOT NULL,
  `cat_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

Any ideas?

[kickstart]

Hi

 

Yes, pretty normalised. You might want to add a unique key to the  marker_category table.

 

Your SELECT statement looks fine. Double check that you do have matching records on all 3 tables.

 

All the best

 

Keith