Correct way to build tables for simple category relation [Solved]

Hi all

I currently have a venues table which I’d like to associated with a number of different categories (table to build) so that each venue can have a number of categories attached to it.

Example:

Venue 2 [dance, disco, 80s]
Venue 7 [80s]
Venue 9 [dance, 80s]

Wondering if I need two tables or can I use one table in small draft shown below?

category_id (primary key)
category_name
venue_id (foreign key to venues.venue_id)

1	dance	2
2	dance	9
3	dance	12
4	disco	2
5	disco	12
6	disco 	14
7	80s 	2
8	80s 	7
9	80s	9

Or I will need a category table first as below?

categories

1	dance
2	disco
3	80s

Thanks, Barry

After looking over an older post with the help of r937 I have the below syntax ready to run:

CREATE TABLE `tbl_venues_cat` (
 `cat_id` int(11) unsigned NOT NULL,
 `cat_name` varchar(100) NOT NULL,
 `venue_id` int(11) unsigned NOT NULL,
 PRIMARY KEY (`cat_id`),
 KEY `venue_id` (`venue_id`,`cat_id`),
 FOREIGN KEY (`venue_id`) 
    REFERENCES `tbl_venues` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Just wondering if this one table will be the right approach for this (as mentioned above #1)?
And do I need the KEYS, a little over kill?

The cat_name should also be unique to the cat_id, do I need some attributes onto this column?
Or maybe I have this all wrong, I need a separate categories table?

Thanks, Barry

tip o’ the day: learn to normalize

CREATE TABLE venues
( id  INTEGER NOT NULL PRIMARY KEY
, venue VARCHAR(9) NOT NULL
);
CREATE TABLE categories
( id  INTEGER NOT NULL PRIMARY KEY
, category VARCHAR(37) NOT NULL
);
CREATE TABLE venue_categories
( venues_id INTEGER NOT NULL
, categories_id INTEGER NOT NULL 
, FOREIGN KEY ( venues_id ) REFERENCES venues ( id )
, FOREIGN KEY ( categories_id ) REFERENCES categories ( id )
, PRIMARY KEY ( venues_id, categories_id )
, INDEX buckle_my_shoe ( categories_id, venues_id )
);
1 Like

:grinning:

I was just creating the categories table funny enough thinking of normalization, one of the pinpoints of my earlier learnings. Should remember :upside_down:

And only just seen your post after refreshed the browser, thanks a lot!

Give this a try now.

Barry

That’s perfect @r937 :sunglasses:
Works great!

I had to add AUTO_INCREMENT onto the categories id setting up the categories table, besides that, good job!

Cheers, Barry

One small issue when I try and return the results @r937

I have tried numerous ways to achieve this using GROUP BY and HAVING, amongst other ways though can’t seem to get things to work :neutral_face:

I only need the venue name to show once and list the associated categories with each venue, not repeat the venue name for every category.

The below code returns:

venue 1  :  category 1
venue 1  :  category 2
venue 1  :  category 3
venue 2  :  category 2
venue 2  :  category 3
...

What I’m trying to return is:

venue 1 : category 1
          category 2
          category 3
venue 2 : category 2
          category 3
...

Any ideas how to achieve this?
Or this is correct I should be using php to do this work?

SELECT v.venue_id, c.category
FROM venues v
INNER JOIN venue_categories vc 
  ON ( v.id = vc.venues_id ) 
INNER JOIN categories c
  ON ( c.id = vc.categories_id ) 
WHERE v.active =1
ORDER BY v.venue_id ASC

Thanks, Barry

in your application language (php or whatever)

in your application language (php or whatever)

Cool, I thought that might be the case, yes using PHP.

Just wondering, and for reference, is what I suggest above possible using SQL with the code we have using three tables?

Been reading lots of stuff trying to figure this out, the only thing that was coming close was GROUP BY, though couldn’t quite get this to work with the current code.

Thanks, Barry

yes, but it’s ugly, complex, and performs poorly

GROUP BY is not the answer, either

So, there is an answer :grinning:

But seriously, I think you’re right, this will be much easier using PHP I think, done something similar with one of our previous threads using the events and dates as the titles. I just wanted to make sure I was not missing something, generally do the hard work with SQL, though in this instance, what I have above should suffice and probably the most we can push this query.

Ok, thanks for sharing the knowledge again Rudy, appreciate the help!

Speak soon, Barry

One final question @r937 :sunglasses:

How many tables could we potentially join above?

I mean, if I wanted to join two other tables resulting in four tables would this be a bad idea, or absolutely fine this is very common?

Or, bad idea, best create another query because this will slow things down?

I read best not go above ten tables in one query.

Example

INNER JOIN table_name one
  ON (  ) 
INNER JOIN table_name two
  ON ( )
INNER JOIN table_name three
  ON (  ) 
INNER JOIN table_name four
  ON (  )
...

Is this a common practice?

Thanks, Barry

as many as you wish

fake news is everywhere

yes

Interesting… though surely bad database design if you need to join more than ten, maybe not, for the more complex and bigger datasets.

I agree :neutral_face:

Nice to know. Feel at ease now using multiple tables within one query - I have a bunch of smaller queries I’d like to combine into one so this would be a good time to use this approach.

Cool, cheers!

Barry

Hey Barry! Here’s what you’re looking for. You need a GROUP_CONCAT and a GROUP_BY
GROUP_CONCAT takes multiple strings and concatenates them together with ‘,’ as a separator.

I would prefer this operation in SQL over PHP. MySQL is very good at relationships between data, maximize it’s strengths.

I created a sqlfiddle so you can see it in action:

SELECT v.id, 
GROUP_CONCAT(c.category) AS categories
FROM venues v
INNER JOIN venue_categories AS vc 
  ON ( v.id = vc.venues_id ) 
INNER JOIN categories AS c
  ON ( c.id = vc.categories_id ) 
WHERE v.active = 1
GROUP BY v.id;

no, not quite

what he wanted was this –

venue 1 : category 1
          category 2
          category 3
venue 2 : category 2
          category 3
...

whereas your GROUP_CONCAT would produce this –

venue 1 : category 1,category 2,category 3
venue 2 : category 2,category 3
...

which is close… but sql is neither hand grenades nor atomic bombs

i repeat my advice – do the formatting in the application language (php or whatever)

p.s. thanks for using my CREATE TABLE code in your fiddle :slight_smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.