Multiple Mysql Cross Reference Tables, or One (by adding an extra field storing reference type)

prx_tags_sportsitems and prx_tags_fishingitems are cross reference tables that link the sportsitem / fishing item (OwnerID) to the Tag (TagID).

Is it better to have separate cross reference tables for each type of item, or to combine them? In my REAL database, I have 10+ types of items, which is why I am considering joining them, however I'm wondering if the speed of queries would be affected.

So it's either:

CREATE TABLE prx_tags_sportsitems (

TagID int(10) unsigned NOT 开发者_如何学运维NULL,

OwnerID int(10) unsigned NOT NULL,

PRIMARY KEY (TagID,OwnerID));

AND

CREATE TABLE prx_tags_fishingitems (

TagID int(10) unsigned NOT NULL,

OwnerID int(10) unsigned NOT NULL,

PRIMARY KEY (TagID,OwnerID));

Using Query:

SELECT prx_sportsitems.* FROM prx_sportsitems, prx_tags_sportsitems WHERE prx_tags_sportsitems.OwnerID = prx_sportsitems.ID AND prx_tags_sportsitems.TagID = (SELECT ID FROM prx_tags WHERE TagName = 'aerobic') ORDER BY prx_sportsitems.DateAdded DESC LIMIT 0,30;

...and same query again for prx_tags_fishingitems

OR, Is it better to join these tables into one by using "OwnerType" to distinguise between sportsitems and fishingitems? Like so:

CREATE TABLE prx_tags_items (

TagID int(10) unsigned NOT NULL,

OwnerID int(10) unsigned NOT NULL,

OwnerType varchar(255) NOT NULL,

PRIMARY KEY (TagID,OwnerID));

Using Query:

SELECT prx_sportsitems.* FROM prx_sportsitems, prx_tags_items WHERE prx_tags_items.OwnerID = prx_sportsitems.ID AND prx_tags_items.TagID = (SELECT ID FROM prx_tags WHERE TagName = 'aerobic') AND prx_tags_items.OwnerType = 'sportsitems' ORDER BY prx_sportsitems.DateAdded DESC LIMIT 0,30;

...and same query again for prx_fishingitems, setting OwnerType to "fishingitems"

I like the idea of joining the tables using OwnerType, since I can then easily create a new OwnerType without creating a new cross reference table for each OwnerType, but my concern is that it will slow down the mysql queries significantly when adding in the line:

AND prx_tags_items.OwnerType = '[owner item type here]'

I appreciate any help and advice. I want to get started soon :)

UPDATE: Would adding an index (primary key) for "OwnerType" help?

I think that the cross-reference table may be a matter of preference in this situation, but as your data sets grow it may become cumbersome for both yourself and for your growing needs of the application to continue to keep that up.

Rather, I would rewrite the queries above to eliminate subqueries and implied joins. I would also add an index on anything that gets evaluated.

A first-blush rewrite would look like this:

select * from prx_sportsitems psi
left join prx_tags_items pti on (pti.ownerid=psi.id)
left join prx_tags pt on (pti.tagid=pt.id)
where pt.tagname = 'aerobic'
and pti.ownertype='sportsitems'
order by psi.dateadded desc
limit 0,30

And will require indexes on the following columns:

• prx_sportsitems.id
• prx_tags_items.id
• prx_tags.id
• prx_tags.tagname
• prx_tags_items.ownertype

Also, don't sort unless you absolutely have to!

Best of luck!