Condensign link tables

[Maracles]

I am designing a database at the moment and I'm currently drawing up a structure. One 'problem' i'm having is that I am having to use lots of link tables for the many-to-many relationships, I was wondering if there is a better way of doing it. I have highlighted an example of the problem below.

 

I have two tables:

 

equipment

people

content

 

Each of the records found in the above tables can then be found in multiple categories. At the moment I have the categories in their own table:

 

categories

 

With this set-up I need to create three link tables to deal with the fact that each item, person or content can have multiple categories:

 

equip/category link

people/category link

content/category link

 

Is there a better way of doing? I am finding the same problem with other areas of the database too.

[ngreenwood6]

Not sure if I am following correctly but maybe I can at least give you an idea if I am incorrect in what I am thinking you are doing.

 

So you have the categories table:

Categories (id,name)

-Food

-Fun

 

Then you have an items table (id,name)

-Dogs

-Golf

 

Now you can have a third table that just pretty much stores the data of which categories those items are assigned to like this

Items_V_Categories (item_id,category_id)

-1 "Dogs" - hot dogs , 1 "Food"

-1 "Dogs", 2 "Fun"

-2"Golf", 2 "Fun"

 

Then you can just pull the items from that table for each category. Hopefully that makes sense.

[Maracles]

Thanks for the reply, I think you kinda getting what I'm looking for. You've described a link table and I understand the concept of the link table i.e. an individual table for associations. My issue is that I need a lot of them and ideally I'd like to streamline them more. I'll give a better example below.

 

Three tables:

 

Equipment

People

Company

 

Then I have a categories table. This category table has the following records:

 

Pre-Production, Production, Post-Production

 

Now, each of the items in either the Equipment, People or Company tab can have multiple categories so at the moment I need three associate tables:

 

Equipment_assoc (equip_ID, category_ID)

People_assoc (people_ID, category_ID)

Company_assoc (company_ID, category_ID)

 

I want to know if maybe I can condense those three link tables down into 1, or possibly 2?

[ngreenwood6]

Ok going off the example you gave instead of doing the three tables like this:

 

Equipment_assoc (equip_ID, category_ID)

People_assoc (people_ID, category_ID)

Company_assoc (company_ID, category_ID)

 

You could do something like this:

 

assoc (link_ID, category_ID,link_TYPE)

 

So a some test entries may be :

1,1,'equipment'

1,1,'people'

 

So when you save it in the link table just save the type along with it

 

INSERT INTO link_table (link_ID,category_ID,link_TYPE) VALUES('1','1','equipment')

 

To get the data just do something like

 

SELECT * FROM link_table WHERE link_TYPE='equipment'

 

Also you can use an enum type for a table, in case you werent aware.

[Maracles]

Thanks a lot, I think I get what you mean and it definitely seems simpler.

 

I have not used enum previously but have just looked them up and understand the concept I think. Basically, instead of having a separate 'type' table I can limit the values permitted to, for example, 'equipment', 'people' 'company'. How many values can you given an enum column?

 

As a side note I am currently using http://ondras.zarovi.cz/sql/demo/ to help plan my database, its a HUGE help. Before I was doing it on paper :-s

 

 

 

 

[ngreenwood6]

I am not sure how many values you can give an enum column off hand but I am sure it is more than you would ever need. That sql builder is pretty neat that you linked to. If you want an easy to use tool to manage databases you should checkout navicat for mysql. Its a good tool in performing complex queries as it can save alot of time building them with its drag and drop builder.

[fenway]

ENUM fields are great -- and the last time I checked, 64 values were supported -- but that makes it all but impossible to support multiple selections.